topic Re: Set Analysis, restriction to ALL, TOTAL, $, {1} in QlikView

Set Analysis, restriction to ALL, TOTAL, $, {1}

Wed, 07 Aug 2013 14:02:20 GMT

Dear Fellows,

I'm strugling with following task. Maybe I didn't get correct the difference between ALL, TOTAL, $, {1} in set analysis.

My goal is to redistribute values from one material (99) on many another (M1 and M2).

It seems my solution works almost correct, however without applying any filter on specific material.

Here is my formula and many another tries. *<vBonusSplit> helps easily maintain parameters on which subtotal should be created the sum. You can delete for example "Variant" and see the result will change.

=sum(total<$(vBonusSplit)>Rebate)*sum(Sales)/sum(total<$(vBonusSplit)>Sales)

I've tried to modify this formula with all, {1} and $ but without any success.

After I apply a filter on specific material, redistributed values (in Rebate on Position column) disappear.

How to resolve this issue to keep always the values right in the according row?

Thank you in advance for your hints, which could be helpful.

Regards

Stan

Re: Set Analysis, restriction to ALL, TOTAL, $, {1}

kedar_dandekar — Thu, 29 Aug 2013 13:00:22 GMT

Hi Stan,

"All" disregards selections, and, if used, in chart, dimensions (ALL field) is the same as ({1} total field)

"Total" does not disregard selections, but it disregards dimensions.

Please see the below link, for the difference between ALL, TOTAL

http://community.qlik.com/message/336161#336161

In your case, try changing your 'Rebate on POSITION' expression to either of the following,

this will make it work irrespective of the selections:

=sum({1} TOTAL <$(vBonusSplit)>Rebate)

*sum(Sales)

/sum({1} TOTAL <$(vBonusSplit)>Sales)

=sum(ALL <$(vBonusSplit)>Rebate)

*sum(Sales)

/sum(ALL <$(vBonusSplit)>Sales)

HTH

Re: Set Analysis, restriction to ALL, TOTAL, $, {1}

michael_maeuser — Thu, 29 Aug 2013 13:08:10 GMT

do you mean this?

Re: Set Analysis, restriction to ALL, TOTAL, $, {1}

michael_maeuser — Thu, 29 Aug 2013 13:17:36 GMT

ah now i got it. thats your expression

=sum(total<$(vBonusSplit)> {<Material=>} Rebate) // /XRate

*sum( {<Material=>} Sales) // /XRate

/sum(total<$(vBonusSplit)> {<Material=>} Sales) // /XRate

Re: Set Analysis, restriction to ALL, TOTAL, $, {1}

Thu, 29 Aug 2013 13:26:31 GMT

Thank you very much, KedarDan,

indeed this is what I was looking for. Both of the formulas are working, I decided to use the first formula:

=sum({1} TOTAL <$(vBonusSplit)>Rebate)

*sum(Sales)

/sum({1} TOTAL <$(vBonusSplit)>Sales)

You have been a great help.

Regards

Stan