Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor
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:

LOAD * 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:
LOAD
$(vMinDate) + RowNo() - 1 as Date_num
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:
NoConcatenate
LOAD
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:

Status0-30 days31-60 days61-90 days> 90 daysGrand total
Afstel320,50   320,50
Afwachten225,00   225,00
Deurwaarder   750,00750,00
Voldaan125,00  525,00650,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)
1 Reply
Highlighted
Digital Support
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.