topic Re: Need help with Pivot ! Different totals for each level ! in QlikView

Need help with Pivot ! Different totals for each level !

sparur — Fri, 07 Sep 2012 06:43:33 GMT

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.

Re: Need help with Pivot ! Different totals for each level !

sparur — Fri, 07 Sep 2012 09:27:01 GMT

No ideas?

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

Re: Need help with Pivot ! Different totals for each level !

jvitantonio — Fri, 07 Sep 2012 09:27:51 GMT

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

Re: Need help with Pivot ! Different totals for each level !

whiteline — Fri, 07 Sep 2012 09:51:28 GMT

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)))

Re: Need help with Pivot ! Different totals for each level !

daveamz — Fri, 07 Sep 2012 09:52:59 GMT

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

Re: Need help with Pivot ! Different totals for each level !

sparur — Fri, 07 Sep 2012 10:25:18 GMT

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?

Re: Need help with Pivot ! Different totals for each level !

daveamz — Fri, 07 Sep 2012 11:19:34 GMT

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

Re: Need help with Pivot ! Different totals for each level !

sparur — Fri, 07 Sep 2012 11:35:08 GMT

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

Re: Need help with Pivot ! Different totals for each level !

sparur — Mon, 10 Sep 2012 06:28:15 GMT

Does anybody have any new ideas?

Re: Need help with Pivot ! Different totals for each level !

whiteline — Mon, 10 Sep 2012 07:54:40 GMT

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.