Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
liuis
Contributor
Contributor

How to make Aggr work with any Dimension

Hi, community

I've got a table with clients debts looking like this

CustomerDateDebt
Customer101/01/2020100
Customer201/01/202010
Customer102/01/2020200
Customer202/01/202030

 

I want to make a measure, calculating average debt over dates, and then sum it over customers.

The obvious way is to use Aggr:

My_Measure = sum(aggr(sum([Debt]) / count(distinct Date), Customer))

and it works fine for a table with Customer as a dimension:

CustomerMy_Measure
Total170
Customer1150
Customer220

 

But if I change the Dimension to [Date], I get the wrong result, since Aggr aggregates Date before it looks for Dimension in Table:

DateMy_MeasureShould be
Total170170
01/01/2020170110
02/01/20200230

 

Of course, I can rewrite measure like this

sum(aggr(sum([Debt]) / count(distinct Date), Date, Customer))

but in this case the Total result does not work correctly (it sums to 340)

 

Is there any workaround, how to make this measure work correctly with Customer OR Date as dimension, and woth correct Totals?

And can I make ot work corectly with any dimensions from linked tables (for example, Month, Year, Customer_Region etc._)

Thanks!

2 Replies
GaryGiles
Specialist
Specialist

I think you want to use your 2nd expression:

sum(aggr(sum([Debt]) / count(distinct Date), Date, Customer))

If you change the "Totals function" to Avg in the Properties panel for the measure, it will give you the correct total.  It should also work if you use other dimensions.

sakthi266
Contributor III
Contributor III

Hi, 

     In that case , the customer based value will show total as 85

CustomerMy_Measure
Total85
Customer1150
Customer220