sum based on dimension value in table

[Anonymous]

hello there,

in qlik sense i want sum of amount based on the dimension value.

here's my query -

if(Reference=1 or Reference=2  or Reference=3,

sum(Amount) / sum({<Reference={'1','2','3'}>}Amount),0)

in this i want sum of amount in table where reference value is either 1,2 or 3 and that sum to be divided by the amount where reference number is 1,2 and 3

required output:

ref       sum(amount)    above expression

1               10                    10/60 = 0.16

2               20                    20/60 = 0.33

3               30                    30/60 = 0.5

rather the output is showing 1's and zero in that field

please help with this..

[Anil_Babu_Samineni]

May be this? and play Number format like yours

Sum(amount)/Sum(TOTAL amount)

[Anonymous]

but thats not the whole table , i forget to mention there are other reference numbers as well

if(Reference=1 or Reference=2  or Reference=3,

sum(Amount) / sum({<Reference={'1','2','3'}>}Amount),

if(Reference=5 or Reference=6  or Reference=7,

sum(Amount) / sum({<Reference={'5','6','7'}>}Amount), .....

)

)

[Anil_Babu_Samineni]

even , Possible

Sum(amount)/Sum({<ref = {1,2,3>} TOTAL amount)

[Anonymous]

and i can use if condition???

[Anil_Babu_Samineni · Accepted solution]

If you are expecting from your expression? Do this?

if(Reference=1 or Reference=2  or Reference=3,

sum(Amount) / sum({<Reference={'1','2','3'}>} TOTAL Amount),

if(Reference=5 or Reference=6  or Reference=7,

sum(Amount) / sum({<Reference={'5','6','7'}>}Amount)

[Anonymous]

i did as u suggested but its not showing correct answer

[Anil_Babu_Samineni]

can you show image after applied this? And let me know the result set you want to see from provided image

[Anonymous]

% change 2018 is where i used your expression -

this is what i'm getting instead of

71% for ref 1

6% for ref 2

and 23% for ref 3

[a_mullick]

Anil's solution should work. Check that you have copied it correctly.

Thanks,

Azam