Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm having a challenge to get max selected year-month value to show correct results in pivot table. Also I'm unable to get previous month values to work in pivot table.
Variable Definitions
vGetSelectedYear
Max([Reporting Cal Year])
vGetSelectedMonth
Max([Reporting Cal Month])
vGetSelectedYearMonthFromYearNMonth
Date#($(vGetSelectedYear) & '-' & $(vGetSelectedMonth), 'YYYY-M')
vGetPreviousYearMonthFromYearMonth
date(AddMonths($(vGetSelectedYearMonthFromYearNMonth),-1), 'YYYY-MMM')
vDoRFromSelectedMonthYear
Date#($(vGetSelectedMonth) & '/1/' & $(vGetSelectedYear), 'M/D/YYYY')
If I've multiple months selected then Pivot is showing numbers added up for all selected months instead of just the max month from selection.
as seen in below screen shot
I'm looking to get following results in Pivot Table Which are Month of 2018-Aug and 2018-Jul next to each other.
Hi Sunny/Robert,
Please take a look at this one and advise if any ideas you may have.
Thanks,
Vijay
LOL, all I was saying was to use this
Count(
{<
[CFS]={"Action Needed", "Awaiting Approver 1", "Awaiting Approver 3", "Past Due", "ITRS Review", "Approved", "ITRS Escalation"}
,[ResRisk]={"High", "Critical"}
,[Risk Acceptance Data?]={"No"}
,[Date of Reporting]={"$(=AddMonths(Monthstart(Max([Date of Reporting])), -1))"}
,[Reporting Cal Year]
,[Reporting Cal Month]>}
[Total of finding counts])
See what it gives when you remove the expression label
All you need as a dollar sign expansion to evaluate your expression here
Count(
{<
[CFS]={"Action Needed", "Awaiting Approver 1", "Awaiting Approver 3", "Past Due", "ITRS Review", "Approved", "ITRS Escalation"}
,[ResRisk]={"High", "Critical"}
,[Risk Acceptance Data?]={"No"}
,[Date of Reporting]={"$(=$(vDoRFromSelectedMonthYear))"}
>}
[Total of finding counts])
And then you get this
Hi Sunny,
Thanks for the reply. It does solve for the selected month. I modified the previous month same way and it does provide me the information.
But there is a strange behavior happening with month selection.
Both Pivot tables provides me information correctly if I select more than a single month value. If I select only a single month value then previous month pivot table is not showing any values. Please refer to screen shots/attached app.
Ignore selection in Month and Date year fields and let your date field drive selections for the previous month
Hi Sunny,
I'm not sure if I'm understanding your suggestion properly here. I'll explain what I've tried.
Business user wants to keep the Year and Month Selection separate for a smaller list of selection. If I use YearMonth field than it is a longer list and I was able to get it working using variables, but I'm trying to get it working using separate selection fields.
Based on your suggestion I've tried following. Not sure if that is what you were advising here.
$(=AddMonths(Monthstart(Max([Date of Reporting])), -1))
I'm getting 2/1/2018 for date as can be seen in label. Again if a single Month is selected then I'm not getting values to be displayed but it works when more than one month is selected.
Thanks,
Vijay
LOL, all I was saying was to use this
Count(
{<
[CFS]={"Action Needed", "Awaiting Approver 1", "Awaiting Approver 3", "Past Due", "ITRS Review", "Approved", "ITRS Escalation"}
,[ResRisk]={"High", "Critical"}
,[Risk Acceptance Data?]={"No"}
,[Date of Reporting]={"$(=AddMonths(Monthstart(Max([Date of Reporting])), -1))"}
,[Reporting Cal Year]
,[Reporting Cal Month]>}
[Total of finding counts])
Hi Sunny,
It was funny that I didn't understand your comments for a such a simple solution. I really did a lol.. 🙂
BR,
Vijay