Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AvinashQV
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
Brett_Bleess
Former Employee
Former Employee

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.