Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stjernvd
Partner
Partner

Exclude Month and Year as filters

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?

3 Replies
settu_periasamy
Master III
Master III

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

Not applicable

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

JonnyPoole
Employee
Employee

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)