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.
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?
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))
tried sum(aggr(Ceil(MonthEnd(Max(Date(dateSet))) - MonthStart(Min(Date(dateSet)))),MonthName)). It gives the value zero. Perhaps I misunderstood something. trying...
hi try something like this
sum(aggr(floor(MonthEnd(Max(dateSet))) - floor(MonthStart(Min(dateSet))),MonthName))
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.
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
Not sure if I understood right. May be like:
Day(MonthEnd(Max(dateSet))) // no. of days in a selected month.
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)
Try like:
=Sum(Aggr(Day(MonthEnd(Max(dateSet))), MonthField))
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.