How to 'Count' properly for dynamic results

[oleggggggM]

Hello.

I have the following task:

I have several objects with Budget and Actual numbers. There are several line items per each object.

I am looking to build a KPI which will show the number of objects that have budget overrun.

The budget is: Sum(Budget Amount)

The actual is: Sum(Actual Amount)

The condition is: If (Sum(Budget Amount)*1.05 >= Sum(Actual Amount), 1, 0)

With this I will get all Objects with Budget Overrun marked as 1. How do I count those objects with "1".

I tried with AGGR function but don't get the correct result. Need your help as I am new to Qlik. thank you

[Or · Accepted solution]

Try something along the lines of:

count(aggr(if(sum([Budget Amount])*1.05>sum([Actual Amount]),1),Object))

[oleggggggM]

Hello,

works well, thank you. Now struggling with the next issue, how to introduce set analysis in this formula, so the result is not affected by the Year dimension the user may select. Any suggestions? thank you.

[Or]

Any set analysis would have to be added in all of the aggregation formulas to ensure it works across the board, but if you have additional questions, it would be best to mark this one solved and start a new thread so the issue gets a fresh set of eyes as necessary.

[oleggggggM]

thank you for the initial solution. I will mark it as an "accepted solution".

still, in your formula above (count(aggr(if(sum([Budget Amount])*1.05>sum([Actual Amount]),1),Object))) - how do you integrate set analysis? can you give an example and I can further find the way to do it. thank you again

[Or]

Again, you would need to add the set analysis individually into each aggregation to ensure it impacts all of them. The syntax for ignoring year would be {< Year = >}.

[oleggggggM]

The answer to my second question: Set Analysis in the AGGR function

In the example provided above it will look like:

count(aggr(if(sum( {$<SET MODIFIER>} [Budget Amount])*1.05>sum({$<SET MODIFIER>} [Actual Amount]),1),Object))