topic AW:Re: Count days in selected months in QlikView

Count days in selected months

Thu, 08 Jul 2010 09:32:15 GMT

I wants to compare sales figures compared to a daily budget. Therefore I make a calculation with days in month.

If the current month is selected I use this formula to calculate day count:

ceil(Makedate(Year(today()), Month(today()),Day(today()))-makedate(Year(today()),Month(today()),1))

If any other month is selected I use this formula to do the same:

ceil(monthend(makedate(Year,Month,1))-makedate(Year,Month,1))

My question is how to combine these when the user selects multiple months?

Thu, 08 Jul 2010 11:15:04 GMT

In the load script can you create a table called DateParts, see script below.

Date:

load

today()-recno()+1 as Date

autogenerate(today()-'2005-12-31');

DateParts:

load

Date as Date,

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,

Year(Date) as Year,

yearname ( Date, 0, 4 ) as FinancialYear,

Month(Date)&' - '&right(year(Date),2) as MonthYear,

Month(Date) as Month,

Day(Date) as Day,

Week(Date) as Week,

Weekday(Date) as WeekDay

resident Date;

drop table Date;

Then create two variables

vMinDate = min(Date)

vMaxDate = max(Date)

Then for reports use the formulas =vMaxDate - vMinDate

This will give the number of days whether you select one month, multiple months or multiple years.

brenner_martina — Thu, 08 Jul 2010 11:41:03 GMT

Hi,

another option:

Num(Today()) - Num(Date)

that means number of days from selected dat to today

Thu, 08 Jul 2010 13:45:15 GMT

Thank you!

num(Today()) - num(monthsstart(1,Today())) did the trick.