# 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
Did you mean:
Contributor

## How to make Aggr work with any Dimension

Hi, community

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

 Customer Date Debt Customer1 01/01/2020 100 Customer2 01/01/2020 10 Customer1 02/01/2020 200 Customer2 02/01/2020 30

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:

 Customer My_Measure Total 170 Customer1 150 Customer2 20

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

 Date My_Measure Should be Total 170 170 01/01/2020 170 110 02/01/2020 0 230

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
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.

Contributor III

Hi,

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

 Customer My_Measure Total 85 Customer1 150 Customer2 20
Tags
Community Browser