topic Count days in QlikView

Count days

Thu, 07 Oct 2010 21:00:30 GMT

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?

Count days

Miguel_Angel_Baeyens — Thu, 07 Oct 2010 22:14:40 GMT

Hi,

You can use (untested)

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

Hope that helps

Count days

Fri, 08 Oct 2010 14:51:59 GMT

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

Count days

johnw — Fri, 08 Oct 2010 23:33:36 GMT

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.

Count days

Tue, 12 Oct 2010 14:36:10 GMT

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

Count days

johnw — Tue, 12 Oct 2010 18:26:17 GMT

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;

Count days

Wed, 13 Oct 2010 19:27:38 GMT

thanks a lot, this help's me soo much