Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data like the below example and want my pivot table to only show the values for the max date per month. I have a variable to toggle the view so I tried =If($(vValuationDate) = 0, MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)), ValuationDate) but it shows the values for 2015-01-22 and 2015-02-12. The other odd thing is if I remove the IF statement and only have =MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)) I get the sum total for each month instead of a specific week's total. I would think the results would at least be consistent. Does anyone know how to get the desired format?
Current format:
ValuationDate | Number |
2015-01-02 | 131 |
2015-01-08 | 148 |
2015-01-15 | 234 |
2015-01-22 | 233 |
2015-01-29 | 165 |
2015-02-05 | 181 |
2015-02-12 | 72 |
2015-02-19 | 194 |
2015-02-27 | 74 |
Needed format:
ValuationDate | Number |
2015-01-29 | 165 |
2015-02-27 | 74 |
Actually, do you know how to flag the last date per month in the load script? I was going to try another method using flags and set analysis but AGGR doesn't work in the load script.
Hi Sunny,
The expression you have shared is working perfect with me but the only issue is when I select any month I get data of that particular month.
My requirement is to get data for 6 months i.e if I select any month I should get data of that month + 5 previous months.
Expression used:
num(Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=,
[Difference Category]={'7Days'}>}Distinct [DEVICE NO])),Date, MonthYear))
/
Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},
MonthYear=>}Distinct [DEVICE NO])),Date, MonthYear)),'0.0%')