Solved: how to get the dates from starting date to End dat... - Qlik Community

Report Inappropriate Content · ‎2015-07-07

Hi Friends,

How can I get the dates from one date to another date.

Example:

ID StartDate EndDate

1 10-04-2015 15-05-2015

I am waiting for your reply..

Thanks

by

Abinesh

swuehl · ‎2015-07-07

Sorry, I don't understand what the requirement here is.

Please post your input files, your sample QVW (you can post it even when you are using PE), and what you want to see (as Excel, screenshot, textual description).

For example, if you want to see the number of days between End and Start per ID, create a proper data model (can't tell without knowing your data and requirements) and just subtract the two values (Dates are numbers when handled properly).

If you want to show all dates inbetween, what do you want to do next then? Whenever other fields related to these dates come into play, I would strongly suggest handling this in the data model / script, like suggested before (Using a WHILE statement or INTERVALMATCH or...).

Again, please post more information, like input files, sample QVW, your requirements.

View solution in original post

MK_QSL · ‎2015-07-07

temp:

Load * Inline

[

ID,StartDate,EndDate

1,10-04-2015,15-05-2015

];

Load ID, Date(StartDate + IterNo() - 1) as Date Resident temp

While StartDate + IterNo() - 1 <= EndDate;

Drop Table temp;

prajapatiamar38 · ‎2015-07-07

Hi.

Try like this

LET vMinDate=num('10-04-2015');

LET vMaxDate=num('15-05-2015');

Calendar1:

LOAD Date($(vMinDate)+(RowNo()-1)) AS DateField

AutoGenerate(($(vMaxDate)-$(vMinDate))+1);

Hope this help you

Thanks

Report Inappropriate Content · ‎2015-07-07

there is any possibility to do this in UI side

Report Inappropriate Content · ‎2015-07-07

Hi,

The following script will work.

Main:

load * inline [

ID ,StartDate , EndDate

1 , 10-04-2015 ,15-05-2015 ];

let vStartDate=peek('StartDate',-1,'Main');

let vEndDate=peek('EndDate',-1,'Main');

load date(num(date(date#($(vStartDate),'DD-MM-YYYY')))+rowno()-1)

autogenerate (num(date(date#($(vEndDate),'DD-MM-YYYY')))-num(date(date#($(vStartDate),'DD-MM-YYYY'))));

vikasmahajan · ‎2015-07-07

Try with Interval Match

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

marcus_sommer · ‎2015-07-07

You could use valuelist() or valueloop() to create these values but the handling with such calculated dimension could be quite difficult and there are disadvantages in the usability, too. Therefore if possible you should take the suggestion from MRKachhiaIMP.

- Marcus

sunny_talwar · ‎2015-07-07

See if the attached helps. It is done on the front end using ValueLoop() function:

Best,

Sunny

Report Inappropriate Content · ‎2015-07-07

Hey sunny still am using Personal edition only I can't open that file can you please send the expression please

sunny_talwar · ‎2015-07-07

Here you go:

Dimension: =ValueLoop($(vStartDate), $(vEndDate), 1)

Where vStartDate and vEndDate are variables.

vStartDate: =Min(StartDate)

vMinDate: =Max(EndDate)

Having the dimension expression as expression as well because dimension was not formatted properly, so I hid that column and created another column which was an expression

I can update the format of expression on the number's tab (but not the dimension):

HTH

Best,

Sunny

how to get the dates from starting date to End date?