Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
remyasujith
New Contributor III

List of Dates between fromdate and todate

Hi,

I have id,value,fromdate and todate fields. I want to get all the dates between these two fields. 

When i use while loop 

Date( FromDate + IterNo() - 1 ) as DateKey

While FromDate + IterNo() - 1 <= ToDate;

The output is:

Id  value datekey

1    1        2019/03/01 

1    1       2019/03/02

1     1      2019/03/03

The issue is for Id=1 the sum(value) should be 1 but because of while loop now the value is 3.

Please help.

4 Replies
Somasundaram
Contributor III

Re: List of Dates between fromdate and todate

Try this,

LOAD

               $(varFrmDate) + Iterno()-1 As Num,

               Date($(varFrmDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varFrmDate) + IterNo() -1 <= $(varToDate);

 


-Somasundaram
pradosh_thakur
Honored Contributor II

Re: List of Dates between fromdate and todate

I would suggest have the date calender in another table, similar to mastr calender.

 

Try similar to below

table:

load id, value from table_name;

 

calender :

load id, datekey  // use your code here , dont use value here 

 

both the table should be joined by id.

 

-Pradosh

Learning never stops.
remyasujith
New Contributor III

Re: List of Dates between fromdate and todate

Can anyone help pls.

marwen_garwachi
Contributor II

Re: List of Dates between fromdate and todate

Maybe sum( firstsortedvalue(value,Date)) ??