Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
bob654321
New Contributor

How to divide sum/sum using multiple if statements ?

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 ?

10 Replies
VishalWaghole
Valued Contributor II

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

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
bob654321
New Contributor

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

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.

bob654321
New Contributor

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

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

Marcos_rv
Contributor II

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

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

bob654321
New Contributor

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

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

Marcos_rv
Contributor II

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

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

VishalWaghole
Valued Contributor II

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

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

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

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')

Marcos_rv
Contributor II

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

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