6 Replies Latest reply: Dec 7, 2016 3:33 AM by Vincent Dubois

# Set analytics - calculate a rate, problem with filtering

Hello,

Below my data set:

 Name Amount Objective 60 20 Player1 10 0 Player2 10 0 Player3 10 10 Player4 10 10 Player5 10 0 Player6 10 0

I would like to calculate in a text object "SUM(Amount) over SUM(Objective)" to show a global achievment rate.

But I want to calculate this only for players who have an objective, in my example: only player 3 and player 4 must be used => 20 /20 => 100%

I don't manage to get the good result with these expressions:

- sum({<Objective={'>0'}>Amount)/Sum(Objective)

- If(Objective>0,sum(Amount)/Sum(Objective))

Either it gives me: 60/20 => 300% (by summing all players), or it gives me 0%

Do you have any suggestions ?

Thanks.

• ###### Re: Set analytics - calculate a rate, problem with filtering

May be this:

Sum({<Name = {"=Sum(Objective) > 0"}>}Amount)/Sum(Objective)

• ###### Re: Set analytics - calculate a rate, problem with filtering

maybe

=

sum({\$ <Name=P({\$ <Name={"=sum(Objective)>0"}>} Name)>} Amount)

& chr(10) &

sum(Objective)

• ###### Re: Set analytics - calculate a rate, problem with filtering

Is there a difference between these two?

{<Name = {"=Sum(Objective) > 0"}>}

vs

{\$ <Name=P({\$ <Name={"=sum(Objective)>0"}>} Name)>}

Under any circumstances, will the above two set expression give different outputs?

• ###### Re: Set analytics - calculate a rate, problem with filtering

Don't think

• ###### Re: Set analytics - calculate a rate, problem with filtering

If they always work the same way, then yes I would think the smaller is better... but I wonder if there is any difference

• ###### Re: Set analytics - calculate a rate, problem with filtering

Thanks Sunny and Massimo.

It works great with the most simple, I haven't tried with the other one.

I didn't know we can mix dimension and fact when we filter in a set analysis... really useful !

Thanks !