topic Re: Set analytics - calculate a rate, problem with filtering in QlikView

Set analytics - calculate a rate, problem with filtering

Tue, 06 Dec 2016 16:45:12 GMT

Hello,

Below my data set:

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.

sunny_talwar — Tue, 06 Dec 2016 17:34:45 GMT

May be this:

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

maxgro — Tue, 06 Dec 2016 17:35:17 GMT

maybe

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

& chr(10) &

sum(Objective)

sunny_talwar — Tue, 06 Dec 2016 17:45:21 GMT

Is there a difference between these two?

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

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

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

maxgro — Tue, 06 Dec 2016 17:55:26 GMT

Don't think

And as your is most simple, your is even better

sunny_talwar — Tue, 06 Dec 2016 18:05:03 GMT

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

Wed, 07 Dec 2016 08:33:14 GMT

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 !