Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

MVP
MVP

Re: Getting number of days only for selected months

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

Re: Getting number of days only for selected months

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

MVP
MVP

Re: Getting number of days only for selected months

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

Re: Getting number of days only for selected months

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

Re: Getting number of days only for selected months

  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.

Re: Getting number of days only for selected months

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

Re: Getting number of days only for selected months

doesn't work.

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

Partner
Partner

Re: Getting number of days only for selected months

Hi try this

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

Highlighted

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?

View solution in original post

Not applicable

Re: Getting number of days only for selected months

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!