Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count days

Hello.

I need some help.

In my excel I have 2 dates, one is DateInitial and the other si DateFinal, DateFinal >= DateInitial

Example (dd/mm/yyyy): 28/01/2010, 03/03/2010

I need count the days between this dates and get the days in the diferent months between this two dates.

for example in january we have 3 days (31 - 28), february 28 days, march 03 days.

how can i do that?

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Here's one approach:

Days:
LOAD *
,rangemin(FinalDate,daystart(monthend(Month)))
-rangemax(InitialDate,Month) + 1 as Days
;
LOAD *
,date(monthstart(InitialDate,iterno()-1),'M YYYY') as Month
RESIDENT Ranges
WHILE monthstart(InitialDate,iterno()-1) <= FinalDate
;
DROP FIELDS InitialDate, FinalDate from Days;

View solution in original post

6 Replies
Miguel_Angel_Baeyens

Hi,

You can use (untested)

=Interval(Date('03/03/2010') - Date('28/01/2010'), 'DD')


Hope that helps

Not applicable
Author

thanks but I need to save the days in the differents months

johnw
Champion III
Champion III

So... are you loading the Excel file into QlikView? Are you loading DateInitial and DateFinal as fields? Setting variables to them?

I might generate all the dates in the range, add a month field to that table, and then count dates by month. But I'm very unclear what your question really is.

Not applicable
Author

yes i'm loading the excel:

Example:

ID InitialDate(dd/mm/yy) FinalDate(dd/mm/yy)

1 01/01/10 05/05/10

Month Days

1 31

2 28

3 31

4 30

5 31

I need save the days for month... in this case, we have 30 days in january (31 - 01), 28 days in febraury, 31 days in march, 30 days in april and 05 days in may...

I need save all the months and the quantity of days in this months

johnw
Champion III
Champion III

Here's one approach:

Days:
LOAD *
,rangemin(FinalDate,daystart(monthend(Month)))
-rangemax(InitialDate,Month) + 1 as Days
;
LOAD *
,date(monthstart(InitialDate,iterno()-1),'M YYYY') as Month
RESIDENT Ranges
WHILE monthstart(InitialDate,iterno()-1) <= FinalDate
;
DROP FIELDS InitialDate, FinalDate from Days;

Not applicable
Author

thanks a lot, this help's me soo much