topic sum based on dimension value in table in QlikView

sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 09:47:19 GMT

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

Re: sum based on dimension value in table

Anil_Babu_Samineni — Wed, 20 Dec 2017 09:49:11 GMT

May be this? and play Number format like yours

Sum(amount)/Sum(TOTAL amount)

Re: sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 09:56:05 GMT

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

)

Re: sum based on dimension value in table

Anil_Babu_Samineni — Wed, 20 Dec 2017 10:01:45 GMT

even , Possible

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

Re: sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 10:10:21 GMT

and i can use if condition???

Re: sum based on dimension value in table

Anil_Babu_Samineni — Wed, 20 Dec 2017 10:12:25 GMT

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)

Re: sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 10:21:40 GMT

i did as u suggested but its not showing correct answer

Re: sum based on dimension value in table

Anil_Babu_Samineni — Wed, 20 Dec 2017 10:37:31 GMT

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

Re: sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 10:54:14 GMT

% 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

Re: sum based on dimension value in table

a_mullick — Wed, 20 Dec 2017 11:39:06 GMT

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

Thanks,

Azam

Re: sum based on dimension value in table

Anonymous — Wed, 20 Dec 2017 11:51:51 GMT

i did exactly what he suggested but its not showing those figures and even i tried your expression too

this is what i wrote in that measure:

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'}>} TOTAL Amount)

)

Re: sum based on dimension value in table

sunny_talwar — Wed, 20 Dec 2017 12:45:14 GMT

Not that it should make any difference, but I recommend making your expression a little simpler...

Sum(Amount)/

If(Match(Reference, 1, 2, 3), Sum({<Reference = {'1', '2', '3'}>} TOTAL Amount),

If(Match(Reference, 4, 5, 6), Sum({<Reference = {'4', '5', '6'}>} TOTAL Amount)))

If this still doesn't work, would you be able to share a qvf sample to show what you are doing?

Re: sum based on dimension value in table

Anil_Babu_Samineni — Wed, 20 Dec 2017 13:19:09 GMT

Your question make sense now? Will you provide sample