Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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.

22 Replies

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

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
Contributor II

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

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

Not applicable

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

there is any possibility to do this in UI side

Not applicable

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

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
Honored Contributor III

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

Try with Interval Match

Vikas

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

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

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

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

Capture.PNG

Best,

Sunny

Not applicable

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

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

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

Here you go:

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

Where vStartDate and vEndDate are variables.

vStartDate: =Min(StartDate)

vMinDate: =Max(EndDate)

Capture.PNG

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

Capture.PNG

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

Capture.PNG

HTH

Best,

Sunny

Community Browser