Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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