How to divide sum/sum using multiple if statements ?

[bob654321]

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 ?

[VishalWaghole]

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]

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]

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]

 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]

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]

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]

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

[bob654321]

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]

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