# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Getting number of days only for selected months

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.

1 Solution

Accepted Solutions
MVP

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?

19 Replies
Partner

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))

Not applicable
Author

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

Partner

hi try something like this

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

Not applicable
Author

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.

Partner

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

MVP

Not sure if I understood right. May be like:

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

Not applicable
Author

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)

MVP

Try like:

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

Not applicable
Author

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.