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

[#1]

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

[kedar_dandekar · Accepted solution]

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)

or

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

*sum(Sales)

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

HTH

KD

[michael_maeuser]

do you mean this?

[michael_maeuser]

ah now i got it. thats your expression

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

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

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

[#5]

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