Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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))
Sum(total Aggr(Day(MonthEnd(Max(dateSet))), dateSet, ITEM)) ?
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.
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.
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))
doesn't work.
still can't apply the "62" in totaldays to all record.
Hi try this
max(aggr(sum(aggr(Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet)))),MonthSet,[Key Code])),[Key Code]))
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?
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!