Announcements
cancel
Showing results for
Did you mean:
Contributor II

Missing dates and referencing date dimension row

Please help, I'm new to Qlik Sense but see that it could be very useful. But I feel I'm missing something fundamental.

Say I have a simple fact table as follows that shows 3 customers with their start and end dates as well as the rate they pay...

Fact:

Customer,StartDate,EndDate,Rate
A,2/24/2018,6/20/2018,100
B,4/1/2018,7/4/2018,200
C,5/6/2018,,300
];

How can I create a chart that looks like the following....

I'm running into the following 2 problems.

1. I'm not getting an exhaustive list of dates, I'm only getting the dates from either column and when I try to set up a master calendar, I believe I'm setting it up wrong as the data is linked to the dates in the new calendar.
2. How can I reference the date dimension? For instance, for the  AvgAge measure I'm thinking I would need a formula like this....

Avg(IF(StartDate < CURRENT_ROW_DATE and EndDate > CURRENT_ROW_DATE,  (CURRENT_ROW_DATE -StartDate)/30 ))

So how can I reference the date dimension current row like I would need in the pseudo code above (CURRENT_ROW_DATE)?Or am I just thinking of this completely wrong?

Labels (3)

• set expressions

1 Solution

Accepted Solutions
Partner - Creator III

This is not a trivial problem. You must generate the list of dates, find which clients are active in different periods and calculate the age for them. After that the expressions will be simple 🙂 Maybe something like this:

Fact:
Customer,
StartDate,
date(if(isnull(EndDate) or trim(EndDate)='',today(),EndDate)) as EndDate,
Rate
INLINE [
Customer,StartDate,EndDate,Rate
A,2/24/2020,6/20/2020,100
B,4/1/2020,7/4/2020,200
C,5/6/2020,,300
];

MinMax:
num(monthstart(min(fieldvalue('StartDate',recno())))) as MinDate,
floor(monthend(max(fieldvalue('EndDate',recno())))) as MaxDate
AutoGenerate fieldvaluecount('StartDate');

Let vMinDate = peek('MinDate',-1);
Let vMaxDate = peek('MaxDate',-1);
drop table MinMax;

Dates:

inner join (Fact)
intervalmatch(Date) Load distinct StartDate, EndDate resident Fact;
drop table Dates;

Fact2:
*,
if(InMonth(StartDate,Date,0),1,0) as FlagNewClient,
((year(Date)*12)+month(Date)) - (((year(StartDate)*12)+month(StartDate))) as AgeMonths
resident Fact;
Drop table Fact;

Expression New Clients: Sum(FlagNewClient)
Expression Avg Age: avg(AgeMonths)

Morgan Kejerhag
Drake Analytics
http://www.drakeanalytics.se

2 Replies
Partner - Creator III

This is not a trivial problem. You must generate the list of dates, find which clients are active in different periods and calculate the age for them. After that the expressions will be simple 🙂 Maybe something like this:

Fact:
Customer,
StartDate,
date(if(isnull(EndDate) or trim(EndDate)='',today(),EndDate)) as EndDate,
Rate
INLINE [
Customer,StartDate,EndDate,Rate
A,2/24/2020,6/20/2020,100
B,4/1/2020,7/4/2020,200
C,5/6/2020,,300
];

MinMax:
num(monthstart(min(fieldvalue('StartDate',recno())))) as MinDate,
floor(monthend(max(fieldvalue('EndDate',recno())))) as MaxDate
AutoGenerate fieldvaluecount('StartDate');

Let vMinDate = peek('MinDate',-1);
Let vMaxDate = peek('MaxDate',-1);
drop table MinMax;

Dates:

inner join (Fact)
intervalmatch(Date) Load distinct StartDate, EndDate resident Fact;
drop table Dates;

Fact2:
*,
if(InMonth(StartDate,Date,0),1,0) as FlagNewClient,
((year(Date)*12)+month(Date)) - (((year(StartDate)*12)+month(StartDate))) as AgeMonths
resident Fact;
Drop table Fact;

Expression New Clients: Sum(FlagNewClient)
Expression Avg Age: avg(AgeMonths)

Morgan Kejerhag