Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date setanalysis End of month

Hello experts,

I suffer the following challenge( and I got no clue how to solve it !)

In my DB I got records wih a startdate and an enddate.

These records should be counted on every monthend day in between the start- and enddate.

Example:

Startdate=01-03-2010 and Enddate=15-07-2010

This record should be counted on 31-03-2010, 30-04-2010, 31-05-2010 and 30-06-2010.

Count ({$<[Num lastdayofmonth]={'>=$(=([Num startdate])<=S(=([Num enddate])'}>} [recordnumber])





Lastdayofmonth = calculated and used as Dimension in Chart )

Num Startdate = Num(startdate)

Num Enddate = Num(enddate)

I hope anyone has an solution or a tip.

1 Solution

Accepted Solutions
Not applicable

Date setanalysis End of month

I think you need to use INTERVALMATCH for this.

First you create a table with every enddate possible for your application and data (borrowed and addapted code Wink.


Calendar:
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(Today()));

// Autogenerate calendar
TempCalendar:
LOAD DISTINCT
monthend($(vDateMin) + RowNo() - 1) AS DateNumber,
monthend(Date($(vDateMin) + RowNo() - 1)) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);


Now you can use INTERVALMATCH to find the ones that fit into your ranges


Temp:
INTERVALMATCH (TempDate)
LOAD DISTINCT
Startdate,
Enddate
RESIDENT [table with startdate and enddate];

JOIN ([table with startdate and enddate])
LOAD
Startdate,
Enddate,
TempDate
RESIDENT Temp;

DROP TABLE Temp;


2 Replies
Not applicable

Date setanalysis End of month

I think you need to use INTERVALMATCH for this.

First you create a table with every enddate possible for your application and data (borrowed and addapted code Wink.


Calendar:
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(Today()));

// Autogenerate calendar
TempCalendar:
LOAD DISTINCT
monthend($(vDateMin) + RowNo() - 1) AS DateNumber,
monthend(Date($(vDateMin) + RowNo() - 1)) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);


Now you can use INTERVALMATCH to find the ones that fit into your ranges


Temp:
INTERVALMATCH (TempDate)
LOAD DISTINCT
Startdate,
Enddate
RESIDENT [table with startdate and enddate];

JOIN ([table with startdate and enddate])
LOAD
Startdate,
Enddate,
TempDate
RESIDENT Temp;

DROP TABLE Temp;


Not applicable

Date setanalysis End of month

Hi Mark,

Thanks for your suggestion. It did work.

Gr

Martien