Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

22 Replies
Not applicable
Author

Use auto generate fuction for getting the dates.

autogenerate(EndDate-StartDate)

svenkita
Creator II
Creator II

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

TmpDates:
LOAD * INLINE [
ID, StartDate, EndDate
1, 10/04/2015, 15/05/2015
2, 01/12/2015, 31/12/2015
]
;

PeriodDays:
Load *,
month(CalendarDateAs CalendarMM,
day(CalendarDateAs CalendarDD,
monthname(CalendarDate) As CalendarRollMonth,
week(CalendarDateAs CalendarWeek,
weekname(CalendarDateAs CalendarWeekName,
weekday(CalendarDateAs CalendarWeekDay;
LOAD ID,
StartDate +(IterNo()-1) as CalendarDate
Resident TmpDates
While IterNo()-1 <= (EndDate - StartDate);

Hope this helps

Not applicable
Author

Dude here I attached my source Startdate.PNGEnddate.PNG

In the above two table I need to show No of dates between start and End date by using any one dimension

sunny_talwar

Do you want one column which would show you dates from 03-07-2015 (Min(SR Created Date)) to 17-07-2015 (Max(SR Closed Date))? If yes than create two variables: vStart and vEnd

vStart: =Min([SR Created Date])

vEnd: =Max([SR Closed Date])

and then create a dimension and expression =ValueLoop($(vStart), $(vEnd), 1). Hide the dimension from the presentation tab and change the formatting of the expression from the Numbers tab and your should be good to go.

HTH

Best,

Sunny

Not applicable
Author

Sunny I couldn't get the result

Can you please tell me if any  other way there means

sunny_talwar

Unfortunately, I don't know another way, but maybe somebody else might now. swuehl‌ would you mind taking a look?

Best,

Sunny

swuehl
MVP
MVP

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.

Not applicable
Author

I got the answer from your text. I just subtract EndDate from Start Date. Got my result Thank you Very much to all.....

sunny_talwar

See that's how expert are. Even when they don't realize, they solve problems. Thanks swuehl‌ for looking into it.

Best,

Sunny

Not applicable
Author

Hey sunny thanks to you also......