topic Re: Percentage operation between two expressions in QlikView

Percentage operation between two expressions

JesusAlv4 — Sat, 16 Nov 2024 01:20:32 GMT

Regards,

I have the following: In a simple table with 3 dimensions and 3 expressions,The three dimensions are values of Technology, Area and product.The three expressions are the goal to produce, PRODGOAL = I use an (if) to not add the zero values. PRODUCED = The sum of what is produced per day. PERCENT = division between (PRODUCED / PRODGOAL)
The calculation is done well, but having the total of the column, this result does not add up, it is not expected.

5940/7112 = 63% this bad must be 83%

If PRODGOAL (Produced = 0, 0, sum (PRODGOALS))

PRODUCED = Sum (QTYGOOD)

PERCENT = (Produced / PRODGOAL)

Re: Operación de porcentaje entre dos expresiones

Sue_Macaluso — Thu, 06 Feb 2020 14:11:44 GMT

@JesusAlv4 Are you using QlikView or Qlik Sense? I would like to move this into the correct product forum.

Re: Operación de porcentaje entre dos expresiones

JesusAlv4 — Thu, 06 Feb 2020 18:17:24 GMT

Sorry Sue, it is a Qlikview product, and change the label to the correct forum, thanks.

Re: Percentage operation between two expressions

sunny_talwar — Thu, 06 Feb 2020 19:10:35 GMT

What exactly are your expressions for the three columns? Are you using column reference for PRODGOAL expression?

Re: Percentage operation between two expressions

JesusAlv4 — Thu, 06 Feb 2020 23:03:35 GMT

Hello Sunny,

With IF sentence, I change the value to ZERO, so that it does not add up to the total production goal.

If (PRODUCIDO = 0, 0, Sum(PRODGOALS))

when I remove those rows of ZERO the calculation of percentage is not correct

example image, 2337/1778 = correct percent 131% not 99%

Re: Percentage operation between two expressions

sunny_talwar — Fri, 07 Feb 2020 14:06:48 GMT

What is your expression for PRODUCIDO column?

Re: Percentage operation between two expressions

JesusAlv4 — Fri, 07 Feb 2020 22:38:36 GMT

The expression is:

PRODUCIDO = Sum (QTYGOOD)

and

PERCENT = (PRODUCIDO / PRODGOAL)

Re: Percentage operation between two expressions

sunny_talwar — Mon, 10 Feb 2020 11:56:04 GMT

Try this

PRODUCIDO/ Sum(Aggr( If(Sum(QTYGOOD) = 0, 0, Sum(PRODGOALS)) , XnAreaDes.., WRKCTRID, ITEMID))

From the image, I couldn't determine the name of your first dimension, but you need to use that within the Aggr() function.

Re: Percentage operation between two expressions

JesusAlv4 — Mon, 17 Feb 2020 23:24:33 GMT

Thank you,

It works correctly, best regards.