# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor

## AGGR Function - Total per dimension

I am troubling with a problem for days now and I don't know what to do, hopefully somebody can help me please.

I work with this Inline table and mini master calendar:

Inline:

Nr, 'StartDate',       'EndDate',         Amount    , Status
1,    01-01-2020,     31-07-2020,         '20,5'      , Afstel
2,    01-01-2020,     31-07-2020,      '125,00'    , Voldaan
3,    22-02-2020,     31-03-2020,        '22,00'    , Deurwaarder
4,    22-02-2020,     31-03-2020,      '325,00'    , Afwachten
5,    01-01-2019,     22-02-2020,      '525,00'   , Voldaan
6,    01-01-2019,     22-02-2020,     '750,00'   , Deurwaarder
7,    19-12-2019,     01-01-2020,     '300,00'   , Afstel
8, 19-12-2019, 01-01-2020, '225,00' , Afwachten
];

Calendar_tmp:
\$(vMinDate) + RowNo() - 1 as Date_num
AutoGenerate 1 While \$(vMinDate) + IterNo() -1 <= \$(vMaxDate);

Calendar:
NoConcatenate
MonthEnd(date(Date_num)) as Date
Resident Calendar_tmp;

Drop table Calendar_tmp;

Let vMinDate = num(MakeDate(2019,1,11));
Let vMaxDate = num(MakeDate(2020,08,1));

PROBLEM

If we select date = 31-01-2020 we want to see the following table as result:

 Status 0-30 days 31-60 days 61-90 days > 90 days Grand total Afstel 320,50 320,50 Afwachten 225,00 225,00 Deurwaarder 750,00 750,00 Voldaan 125,00 525,00 650,00

First we have to determine the age of each line and then classify it in the correct category.

I tried the follow expression for the column [0-30 days] but it did not work:

IF(

If(
StartDate < Max(Date) and EndDate > Max(Date),
Max(Date) - StartDate,
EndDate - StartDate) <= 30,

AGGR( sum(
{\$<StartDate= {"<=\$(=Max(Date))"}>}Amount), Status)
)

Somebody an idea what I am doing wrong?

Eventually the table should provide insight in the ageing of receivables.

I really really hope that somebody can help me.

Labels (2)

• ### Total of dimension

Digital Support

Best I have is the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If you want search for other posts in this area, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.