Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Getting number of days only for selected months

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
lironbaram
Honored Contributor II

Re: Getting number of days only for selected months

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

Re: Getting number of days only for selected months

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

lironbaram
Honored Contributor II

Re: Getting number of days only for selected months

hi try something like this

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

Not applicable

Re: Getting number of days only for selected months

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
Honored Contributor II

Re: Getting number of days only for selected months

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
MVP

Re: Getting number of days only for selected months

Not sure if I understood right. May be like:

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

Not applicable

Re: Getting number of days only for selected months

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
MVP

Re: Getting number of days only for selected months

Try like:

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

Not applicable

Re: Getting number of days only for selected months

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.