1 Reply Latest reply: May 17, 2010 7:56 PM by Steve Dark RSS

    Using Subtotal of a Dimension as Basis for %age calculation

    Petra Bernehed


      in charts, especially in this case Pivots, I want to calculate a %age of a dimension subtotal. In this expression

      count(distinct {$ <CW ={"$(vMaxCW4)"}, SalesQuarter= , Salesmonth=, SalesWeek= >} StoreNumber)/count(distinct total {$ <CW ={"$(vMaxCW4)"}, SalesQuarter= , Salesmonth=, SalesWeek= >} StoreNumber)

      I am counting the number of uniques stores for a subdimension, say Brand. I then want to find in want percentage of stores this brand is listed in. This is what the above expression does. In this case the set analysis allows me to use a particular calendar week. But what if I had as my first dimension in the table the Storetype, and I want to see what percentage my brand has not of the total count storenumber, but of the total count storenumber belonging to the storetype. In other words the total number of one storetype would be always be 100%.

      I would want to take the percentage of the subtotal rather than of the grand total.

      I would add a screen capture, but i don't kow how to paste that in here.

      Any hints from anyone? It is probably the aggr() function, but I cannot seem to gt that to work.