Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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.

19 Replies
tresesco
MVP
MVP

Create a month field in the script rather than a master item, like:

MonthName(dateSet) as Month

because you can aggregate only on script generated fields not on a master item. Once you create Month field in the script your expression, like: =Sum(Aggr(Day(MonthEnd(Max(dateSet))), Month)) should work.  

Not applicable
Author

wow, at least it is closer now.

but what if i want the totalDays to be 62 across all item?

What I did is:

a) follow what you said, make dateSet as a field, not a master item, then use it as the filter.

b) Sum(Aggr(Day(MonthEnd(Max(dateSet))), dateSet, ITEM))

tresesco
MVP
MVP

Sum(total Aggr(Day(MonthEnd(Max(dateSet))), dateSet, ITEM))   ?

rubenmarin

Hi, if you want same value across all row you should avoid row dimensions using TOTAL:

Sum(TOTAL Aggr(Day(MonthEnd(Max(TOTAL dateSet))), dateSet, ITEM))

But in your current expression will cause an incorrect calculation because there will be more than on dateSet for each month, so you'll need a Month field as said by tresesco and then you can use:

Sum(TOTAL Aggr(Day(MonthEnd(Max(TOTAL dateSet))), NewMonthField))

Better if there is a Year-Month field, because february can have different days in leap years and if there are more than one year selected it will be more accurate.

Not applicable
Author

  MonthName("Actual Ship Date") as dateSet,  + dateSet is showing Oct 2015 , Nov 2015 ...

Hence, I presume, it shouldn't be a problem counting for leap year?

😞 anyway, I still can't figure out, how to get totalDays=62 for all records. Actually the requirement only needs to get the total number of Days in the selected yearMonth filter.

rubenmarin

Hi Saw, In script create a YearMonth field in the same table where you load "Actual Ship Date". Then you can use this new field in Aggr():

//Create YearMonth as Date:

LOAD ...

     MakeDate(Year("Actual Ship Date"), Month("Actual Ship Date")) as YearMonth

//Use this field to count the days, It will have only one value per year-month and is a date, so you can use date funtions like MonthEnd() and Day()

Sum(TOTAL Aggr(Day(MonthEnd(YearMonth)), YearMonth))

Not applicable
Author

doesn't work.

still can't apply the "62" in totaldays to all record.

lironbaram
Partner - Master III
Partner - Master III

Hi try this

max(aggr(sum(aggr(Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet)))),MonthSet,[Key Code])),[Key Code]))

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?

Not applicable
Author

Can't believe it.

I used your formula, and it works. I tried something almost like yours previously, but I keep getting, "can't nest aggregate function" error. Thanks a lot guys!! Finally i am able to get what I want. Now it is showing correctly. Thanks Ruben, Liron and tresesco!