KPI Counting Aggregated Deltas

HeyAbbott — Mon, 20 Dec 2021 21:08:46 GMT

Hey All,

This is my first time posting -- please let me know if I'm doing anything wrong.

I have a tricky bit of aggregation that I just can’t figure out. I have a dataset with values and dates that I append to each month in a qvd. The data looks like this:

ID	Amount	Report_Date
A	100	1/1/2020
B	200	1/1/2020
C	300	1/1/2020
D	400	1/1/2020
A	200	2/1/2020
B	200	2/1/2020
D	200	2/1/2020
E	500	2/1/2020

I am aggregating sums based on MIN and MAX dates for my chart. I get the Amount Changed by subtracting the Beginning Balance from the Ending Balance. Like this:

ID	Beginning Balance	Amount Changed	Ending Balance
A	100	100	200
B	200	0	200
C	300	-300	0
D	400	-200	200
E	0	500	500

The formulas I’m using look like this:

Beginning Balance: Sum({<Report_Date = {'$(=date(Min(Report_Date)))'}>}[Amount])

Ending Balance: Sum({<Report_Date = {'$(=date(Max(Report_Date)))'}>}[Amount])

Amount Changed: Sum({<Report_Date = {'$(=date(Max(Report_Date)))'}>}[Amount]) - Sum({<Report_Date = {'$(=date(Min(Report_Date)))'}>}[Amount])

These formulas are working fine. Each positive change (>0) represents a new expense, and each negative change (<0) represents a refunded expense. I want KPIs counting how many IDs have a positive change amount and a negative change amount, but I can’t figure out how to write the formula that says ‘Count all of the IDs where Amount Changed is >0.’ I end up with a formula with nested aggregations and sets and Qlik doesn’t like it.

Any idea of how I can write a KPI that counts the positive and negative deltas between the Beginning and Ending Balances?

Re: KPI Counting Aggregated Deltas

GaryGiles — Mon, 30 Nov 2020 17:53:40 GMT

Try this:

count( distinct if(aggr(Sum({<Report_Date = {'$(=date(Max(Report_Date)))'}>}[Amount]) - Sum({<Report_Date = {'$(=date(Min(Report_Date)))'}>}[Amount]), ID)>0,ID))

Re: KPI Counting Aggregated Deltas

HeyAbbott — Mon, 30 Nov 2020 18:44:22 GMT

That did the trick -- pure genius! Thank you!

topic Re: KPI Counting Aggregated Deltas in App Development

KPI Counting Aggregated Deltas

Re: KPI Counting Aggregated Deltas

Re: KPI Counting Aggregated Deltas