topic Calculating percentage based on conditions in App Development

Calculating percentage based on conditions

adrianfer — Wed, 14 Aug 2024 01:03:46 GMT

Good day!

Given this load script:

Table:

LOAD * Inline [

ID,Item,Total,Pending

A,A-1,10,8
A,A-2,12,7
B,B-1,190,124
C,C-1,18,2
C,C-2,100,60
D,D-1,2,2
D,D-2,10,8
];

I would like to get the Pending Rate (Sum of Pending/Sum of Total) for the IDs where the Sum of Total is >= 20. However, the value I am getting is as if I am not applying the condition that the Sum of Total is >= 20.

My expression for Pending Rate for the second chart is :

If(Sum(Aggr(Sum(Total),ID))>=20, Sum(Pending)/Sum(Total))

I have attached a sample file here.

Appreciate all the help! Thank you!

Re: Calculating percentage based on conditions

MatheusC — Wed, 14 Aug 2024 13:30:46 GMT

@adrianfer

see the result with the expression below:

Sum(Aggr(if(Sum(Total)>=20,Sum(Pending)/Sum(Total)),ID))

- Matheus

Re: Calculating percentage based on conditions

Oleg_Troyansky — Wed, 14 Aug 2024 15:00:41 GMT

Hi @adrianfer ,

Your condition doesn't work as expected because Sum(Aggr(Sum(Total),ID)) returns the overall sum of the Total values across all IDs - the AGGR function returns an array of sum(Total) by ID, and then the Sum() function aggregates the array into a single number - 342 in your example.

In fact, you don't really need to use AGGR() if your chart includes ID as a dimension. In other instances, you would, but not here. In this chart, it's enough to add a simple IF condition like this:

IF(sum(Total)>=20, Sum(Pending)/Sum(Total))

If you wanted to apply the same condition to all measures, then you should add the same IF() to all of them.

Allow me to invite you to the upcoming session of the Masters Summit for Qlik in Vienna, where I will be teaching Advanced Development Techniques, including advanced uses of AGGR(), Set Analysis, Data Modeling, Performance Optimization, and more.

Cheers,

Re: Calculating percentage based on conditions

adrianfer — Thu, 15 Aug 2024 00:09:49 GMT

That gives me this:

Per ID is correct, but the Totals should be 201/330 = 60.9% right?

Re: Calculating percentage based on conditions

adrianfer — Thu, 15 Aug 2024 00:19:27 GMT

This one gives this:

Per ID is correct, but the Totals should be 201/330 = 60.9%

Re: Calculating percentage based on conditions

poklegoguy — Thu, 15 Aug 2024 00:41:24 GMT

If you don't need to display 'D' which Total is less than 20, you can use this in the dimension and untick 'Include Null Values':
IF(AGGR(SUM(Total),ID) >= 20, ID)

For measures,
Total: SUM(Total)
Pending: SUM(Pending)
Pending Rate: Column(2) / Column(1)

Re: Calculating percentage based on conditions

Kushal_Chawda — Thu, 15 Aug 2024 09:27:23 GMT

@adrianfer try below expression

Sum({<ID={"=sum(Total)>=20"}>}Pending)/
Sum({<ID={"=sum(Total)>=20"}>}Total)

Re: Calculating percentage based on conditions

Oleg_Troyansky — Thu, 15 Aug 2024 14:30:49 GMT

Well, you should either exclude the D from the chart (which is probably the best decision), and then the total should match the sum of lines, or you could use the AGGR function again, but this time correctly:

sum(AGGR(IF(sum(Total)>=20, Sum(Pending)), ID)) /

sum(AGGR(IF(sum(Total)>=20, Sum(Total)), ID)) /

In this formula, the product D will get excluded from the totals, even if it remains in the chart with all empty values.

The Set Analysis with advanced search solution, suggested by @Kushal_Chawda , is also a very good one. It's limited to a single field (ID), but it works very well if this is all you need.

As a separate note, I'd mention that depending on the nature of your objects (products, people, etc.) it may not be a good idea to calculate total % by summarizing all units and dividing the totals by each other. For example, if these are products, and one product could be a screw and another product could be a car, - in this case I wouldn't want to sum up screws and cars in one total. In this case, I'd rather define the total % as an average of the individual percentages - but that's a different discussion altogether.

Back to AGGR - it's a very powerful and the least understood function in Qlik. You can use it for very complex calculations, when you know how to use it right. For anyone who is interested in learning more - let me invite you again to the upcoming Masters Summit for Qlik in Vienna, where I'll be teaching advanced development topics, including the various complex scenarios with the AGGR() function and with advanced Set Analysis.

Cheers,