Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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.

Labels (3)
4 Replies
Highlighted
Creator III
Creator III

Try this,

LOAD

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

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

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

 


-Somasundaram
Highlighted
Master II
Master II

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.
Highlighted
Contributor III
Contributor III

Can anyone help pls.

Highlighted
Creator II
Creator II

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