topic Re: Calculate percentage in QlikView

Calculate percentage

AT79 — Sun, 17 Feb 2019 19:13:55 GMT

Hi,

I have an excel table attached to this question.

Here is wat i want to achieve:

We want to calculatie (based on any selection) the total percentage of In Full. So depending on if the artikel is a piece (aantal) or KG. it should calculate de deliveryperformance.

The outcome of this set of data should be a KPI on total: 102,73%.

The files in Qlik sense are dimension and meassurements:

Dimensions are:

Datum, Deb.Nr, Deb.Naam, Art.Nr, Art.Naam, Aantal, Gewicht, RegAantal, NettoKg, Tijd.

Meassurements are:

Stuksartikel : IF(Stuksartikel = 'Ja','Ja','Nee')

#/KG Besteld: IF(Stuksartikel = 'Ja', Sum(Aantal), Sum(Gewicht))

#/KG Geleverd: IF(Stuksartikel = 'Ja', Sum(RegAantal),Sum([NettoKg]))

% #/KG Uitgeleverd: SUM(Distinct IF(Stuksartikel = 'Ja', RegAantal,NettoKg))/SUM(Distinct IF(Stuksartikel = 'Ja', Aantal,Gewicht))

Hope anyone can give me some idea’s how to make this KPI.

Re: Calculate percentage

uacg0009 — Mon, 18 Feb 2019 07:56:24 GMT

Hi,

Based on the calculation of your excel, I think the KPI expression should be :

=(
Sum({<Stuksartikel = {"Ja"}>}RegAantal) + 
Sum({<Stuksartikel = {"Nee"}>}NettoKg)
)
/
(
Sum({<Stuksartikel = {"Ja"}>}Aantal) + 
Sum({<Stuksartikel = {"Nee"}>}Gewicht)
)

Please try.

Aiolos

Re: Calculate percentage

AT79 — Mon, 18 Feb 2019 09:23:36 GMT

Hi Aiolos, With this formula, the outcome is 97,47%. It should be 102,73%

Re: Calculate percentage

uacg0009 — Mon, 18 Feb 2019 10:17:27 GMT

I don't know whether you can open my attachment, but it works in my desktop:

If you still can't get the correct answer, could you please share your example?

it's fine for me to open qlikview or qliksense, then I can check why it's not correct.

Thanks.

Aiolos Zhao

Re: Calculate percentage

AT79 — Sat, 23 Feb 2019 18:54:35 GMT

Hi Aiolos,

thanks for the solution. I had to change something in my app, now it works with your Formula. My only problem now is that when i select only the stuksartikel or only the kg artikel the outcome stays the same.

Do you have any idea?

Re: Calculate percentage

uacg0009 — Mon, 25 Feb 2019 05:36:33 GMT

Hi,
I don't see the column named [kg artikel],
but about the stuksartikel , yes, the result won't change.
because I hard-code the logic about the stuksartikel in the expression, like what you see:
Stuksartikel = {"Ja"}
Stuksartikel = {"Nee"}
Because I followed the logic in your excel, so if you think the logic is wrong, could you please give me a new logic to calculate what you want, then I will try to find a new expression to solve that.
Aiolos Zhao