Exclude Month and Year as filters

[stjernvd]

Hi,

I have this expression in my pivot table:

Count({<Month=,Year=,MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM-YYYY'))"} >}Place)

This allows me to see a 12 month view of Months at all times.

However, I want to include in this equation the option to exclude filters for Month and Year.

How do I do that?

[settu_periasamy]

Did you try to remove Trademonth and tradeyear from the expression?

[#3]

are you wanting to default to a 12 month view but if other date selections are made, use those selections?

[JonnyPoole]

So i your equation, the date range is calculated on the current selection . If you select a year=2012 , then the max(trademonthyear) will be 2012.  If you select 2014, it will select 2014 .

Count({<TradeMonth=,TradeYear=,TradeMonthYear ={">=$(=Date(addmonths(Max(TradeMonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(TradeMonthYear), 0), 'MMM-YYYY'))"} >}Destination)

To prevent that, pre-calculate the max(TradeMonthYear) by using a variable

vMaxTradeMonthYear

where the equation is :      =max( {1} TradeMonthYear)

or

=max( {<TradeMonth=,TradeYear=>} TradeMonthYear)

Make sure to include the preceding '=' in the variable so it precalcualtes.

This will precompute the value for the whole data set regardless of selections . So if you select 2012 or 2014, it is stiall the maximum Year ... whatever that is in your data set.

Then you can use the variable

Count({<TradeMonth=,TradeYear=,TradeMonthYear ={">=$(=Date(addmonths( $(vMaxTradeMonthYear) , -11), 'MMM-YYYY')) <=$(=Date(addmonths( $(vMaxTradeMonthYear, 0), 'MMM-YYYY'))"} >}Destination)