9 Replies Latest reply: Sep 10, 2012 3:54 AM by whiteline _ RSS

    Need help with Pivot ! Different totals for each level !

    Anatoly Pyatygo

      Hello colleagues.

      I have one interesting task for you


      I'll try to explain my situation...


      I have one fact table with simple structure: Date, Item, Flag

      And one master data table - Item, with structure like this: Item, Group.

      In fact table for each Item and date I have Flag 1 or 0.




      And I need calculate number of Dates, where Flag is 0.


      I have a simple pivot chart with 2 dimensions: Group and Item. And I need to calculate count of Dates, where Flag = 0.

      For each Item it is a very simple, I use such expression: count({<Flag = {0}>} Date)


      But for Group level I get sum of count Dates, but I need count Dates, where Flags of all items from this group is 0.


      In another words, I need to get a number of Dates where subtotal (for each group) is 0.



      So as a Result I want to get for

      G1 is 1 (because there is only one Date, where flags for all Items are 0)

      G2 is 4.


      Does anyone have any ideas?


      Thanks a lot.