Qlik Community

New to Qlik Sense

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead 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
rubenmarin

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

19 Replies
lironbaram
Partner
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...

lironbaram
Partner
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.

lironbaram
Partner
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

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

tresesco
MVP
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.