Calculating percentage based on conditions

[adrianfer]

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!

[MatheusC]

@adrianfer 

see the result with the expression below:


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


- Matheus

[Oleg_Troyansky]

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,

[adrianfer]

That gives me this:

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

[adrianfer]

This one gives this:

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

[poklegoguy]

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)

[Kushal_Chawda · Accepted solution]

@adrianfer  try below expression

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

[Oleg_Troyansky]

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,