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.

       

      pic4.png

       

      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)

      pic2.png

      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.

      pic3.png

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

      pic1.png

       

      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.