AGGR Function - Total per dimension

AvinashQV — Sat, 16 Nov 2024 18:22:13 GMT

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.

Re: AGGR Function - Total per dimension

Brett_Bleess — Thu, 23 Jul 2020 14:08:14 GMT

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

topic Re: AGGR Function - Total per dimension in QlikView

AGGR Function - Total per dimension

Re: AGGR Function - Total per dimension