topic Re: How to divide sum/sum using multiple if statements ? in QlikView

How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 17:11:39 GMT

Below is how I'm calculating my expression.

If Label = 'A' and Client = 'X' ,Sum(Measure),

If Label = 'B' and Client = 'Y', Sum(Measure)

If Label = 'C' and Client = 'Z', Sum(measure)/If Label = 'D' and Client = 'W', Sum(measure) )))

The first 2 if statements worked perfectly but the last if statement to calculate the division is not working. It doesn't display any value on the dashboard. How can I do the sum / sum using if statements ?

Re: How to divide sum/sum using multiple if statements ?

VishalWaghole — Fri, 05 Apr 2019 17:20:37 GMT

Try this,

If(match(Label,'A') and match(Client,'X'), Sum(Measure),
If(match(Label,'B') and match(Client,'Y'), Sum(Measure),
If(match(Label,'C','D') and match(Client,'Z','W'),Sum(Measure)/Sum(Measure))))

However i didn't understand why you are using same expression for all condition.

Thanks,
Vishal

Re: How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 17:24:12 GMT

Thank you! I'm using different measures and calling it as Measure in the load script and concatenating them individually using the label field.

For example

Load Sum(Sales),'A' as Label,'X' as Client from qvd;

CONCATENATE

Load Sum(Profit),'B' as Label,'Y' as Client from qvd;

I will try your logic and let you know if it works or not.

Re: How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 17:37:56 GMT

Unfortunately your formula that uses sum(Measure)/Sum(Measure) is being displayed as 1. How to rewrite this formula properly?
If(match(Label,'C') and match(Client,'Z'),Sum(Measure)/If(match(Label,'D') and match(Client,'Z'),Sum(Measure))))

Re: How to divide sum/sum using multiple if statements ?

Marcos_rv — Fri, 05 Apr 2019 17:44:27 GMT

try this

(Sum({<Label = {'A'} ,Client ={'X'}>}Measure) + Sum({<Label = {'B'} ,Client ={'Y'}>}Measure) + Sum({<Label = {'C'} ,Client ={'Z'}>}Measure) )/(Sum({<Label = {'D'} ,Client ={'W'}>}Measure) )

ENJOY !!!

Re: How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 17:48:22 GMT

But your formula won't work in my case because I have multiple if match statements to display different measures.

Something like this what Vishal has posted would work..the 3rd formula that has sum(numerator)/sum(denominator) needs tweaking. I'm not sure how to rewrite it ?

If(match(Label,'A') and match(Client,'X'), Sum(Measure),
If(match(Label,'B') and match(Client,'Y'), Sum(Measure),
If(match(Label,'C','D') and match(Client,'Z','W'),Sum(Measure)/Sum(Measure))))

Re: How to divide sum/sum using multiple if statements ?

Marcos_rv — Fri, 05 Apr 2019 17:52:17 GMT

this is to use it in a KPI ??

if so, think that every time you use the sum () these (I'm using it 3 times) have in the set analisys set the conditions, each one has different value, you could build a simple table and you will see that they give the expected values .

it is understood???

Re: How to divide sum/sum using multiple if statements ?

VishalWaghole — Fri, 05 Apr 2019 17:53:42 GMT

Could you please share some sample data which will cover your last condition.

Re: How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 18:39:18 GMT

Vishal,

See the sample exel report with the data in the attachment.

your formula works for straight measures that doesn't involve division or multiplication.
But it won't work when we have to divide the 2 measures.

For example , if the label = 'B' and client = Y' then Sum(Measure where label = 'B' and client = Y')/Sum(Measure where label = 'D' and Client = 'W')

Re: How to divide sum/sum using multiple if statements ?

Marcos_rv — Fri, 05 Apr 2019 20:04:37 GMT

prueba esto siempre y cuando no estés filtrando por Label o Client, pero si usas otros filtro o muestras en unas tabla de esta manera debería funcionar:

Sum({<Label = {'A'} ,Client ={'X'}>} Measure) +

Sum({<Label = {'B'} ,Client ={'Y'}>} Measure) +

(Sum({<Label = {'C'} ,Client ={'Z'}>} Measure) )/(Sum({<Label = {'D'} ,Client ={'W'}>}Measure) )

Saludos!!!

Re: How to divide sum/sum using multiple if statements ?

bob654321 — Fri, 05 Apr 2019 20:17:13 GMT

Marcos, is there any reason why there is a plus sign between the Sums ? The Sum(Measures) should be shown separately in each cell.