Tuesday, July 26, 2016

Creating COUNT(DISTINCT "Orders"."Order Number" by "Products"."Product Type") in RPD

I wanted to create  COUNT(DISTINCT "Orders"."Order Number" by "Products"."Product Type") as a column in the repository, based on the default Sample Sales.
In the Sample Sales there is already a column that return COUNT(DISTINCT "Orders"."Order Number"). It's Name is # of orders and it is defined as "Count Distinct" aggregation on the Order_number key:







Basically the COUNT(DISTINCT "Orders"."Order Number" by "Products"."Product Type") is equivalent to Sum("# of Orders" by "Products"."Product Type"). For example:

I will use this logic in the RPD:
I duplicated the # of Orders column. In our case the aggregation by Products dimension should be sum, while by other dimension "Count Distinct", so turn on the "Based on dimensions" option and set it (use the Plus sign to add Logical Dimensions and select the formula from the pull down menu):

 

Now the last part is to set the levels, at the column  to be by Product Type for products and Total for all the rest:


 I didn't bother with column name so it's "# of Orders 1".
This is the result:

 




1 comment:

  1. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command .

    PPC Services in Chennai

    ReplyDelete