19 Replies Latest reply: Oct 26, 2015 10:53 PM by Teck Rul Saw

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.

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

• 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...

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

• 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.

• 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

• 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.

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

• Re: Getting number of days only for selected months

Try like:

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

• 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.

• 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.

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

• 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.

• 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:

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

• Re: Getting number of days only for selected months

doesn't work.

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

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

• 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?

• 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!