Solved: Getting number of days only for selected months - Qlik Community

Report Inappropriate Content · ‎2015-10-15

So the story goes like this,

Initially, this "Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet))))" would do. It works for calculating the total number of days from August to October. However, the requirement changed, how can I get the total number of days only for the selected months? I couldn't find a way. Please help.

rubenmarin · ‎2015-10-26

I tried with this script:

Data:

LOAD *, Makedate(Year, Month) as YearMonth

Inline [

Item, Sales, Year, Month

A,100,2015,1

A,100,2015,2

B,100,2015,1

];

And returns 59 in all rows (31 days of january + 28 of february)

Something must be different, can you upload the sample with the YearMonth field created?

View solution in original post

lironbaram · ‎2015-10-15

hi you can do it by using this expression

sum(aggr(Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet)))),MonthName))

but then in each month you lose one day so what i'll use is

sum(aggr(Day(Max(dateSet)),MonthName))

Report Inappropriate Content · ‎2015-10-15

tried sum(aggr(Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet)))),MonthName)). It gives the value zero. Perhaps I misunderstood something. trying...

lironbaram · ‎2015-10-15

hi try something like this

sum(aggr(floor(MonthEnd(Max(dateSet))) - floor(MonthStart(Min(dateSet))),MonthName))

Report Inappropriate Content · ‎2015-10-15

Still returning zero,

dateSet is a set of dates from 2 sources ( 2 excel files)

Then in my app i did it like this. However I did not add it to the table's dimension. Not sure will that affect the expression.

when i put the dimension in the aggr function as "dateSet", only then numbers will be displayed, but the result is wrong.

lironbaram · ‎2015-10-15

of course it affects the expression

you can't use aggr on a calculated field

you'll need to create a month field in your script and use it in your aggr function

tresesco · ‎2015-10-16

Not sure if I understood right. May be like:

Day(MonthEnd(Max(dateSet))) // no. of days in a selected month.

Report Inappropriate Content · ‎2015-10-16

it works only when I select one YearMonth, for example Oct 2015

However I want it to be able to be 62 if I select two year month, (Aug 2015 and Oct 2015)

tresesco · ‎2015-10-16

Try like:

=Sum(Aggr(Day(MonthEnd(Max(dateSet))), MonthField))

Report Inappropriate Content · ‎2015-10-19

Tried "=Sum(Aggr(Day(MonthEnd(Max(dateSet))), MonthField)) " too. It's not usable in my scenario.

Below are the sources and app.

The problem lies in when the user select the Month Oct 2015 and August 2015. How do I get 31+30 only? Rather than 31+31+30 as number of days.