Need help with Pivot ! Different totals for each level !

[sparur]

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.

[sparur]

No ideas?

I'm ready to use any crazy ideas, which could help to achieve the final result.

[jvitantonio]

I've been working on this for 1 hour but still not able to solve it :-S

[whiteline]

The result is definetly right.  This way set analisys is working.

You case is completely different, try something like this:

=if(Dimensionality()=1,

Sum(aggr(if(Sum(Flag)=0, count(Date), null()), Group, Date)),

Sum(aggr(if(Sum(Flag)=0, count(Date), null()), Group, Item, Date)))

[daveamz]

Hi,

Same here! I tried aggr, if, mixing if with aggr with no succes.

Did you thought to a different approach? See the file attached.

Regards,

David

[sparur]

2 whiteline: yeah, your approach is working. I thought about Dimensionlity too, but I try to avoid such huge numbers of IF statements.

but anyway, thanks a lot. Your variant is the best until now.

2 daveamz01: thanks a lot, but your approach is not flexible, for instance, if I add Item as a second dimension, your approach doesn't work.

2 all: Does anybode have any ideas else?

[daveamz]

To make it flexilbe use: if(Dimensionality()=1,count({<FlagS={0}>}Date2),count({<Flag = {0}>}Date))

[sparur]

Thanks  daveamz01

your approach is working too. but it uses dimensionality() function too.

and I hope that we can achieve results without pre-calculation (in script level)

may be, very very strong and complex AGGR or something else

[sparur]

Does anybody have any new ideas?

[whiteline]

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

I think you just can't do it the way you want.

The subtotals for each level has different meaning in your case.