Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

surajap123
Contributor II

Filter Months

Hi All,

I want to show last 6 months and last 12 months data to users using buttons(6 months button and 12 months button).

By default we want to show the complete data.

I have pivot table with Month-Year as a horizontal dimension.

Please help.

1 Solution

Accepted Solutions
sasikanth
Valued Contributor III

Re: Filter Months

Hi suraj,

use triggers for this case try below expressions in "select in field trigger"

For 6 Months

= '<='&max(MYear) &'>='& date(addmonths(max(MYear),-5),'MMYYYY')


For 12 Months

= '<='&max(MYear) &'>='& date(addmonths(max(MYear),-11),'MMYYYY')


OR simply use this exp

Field : Date

search string : ='>='& Date(addMonths(max(Date),-5),'DD/MM/YYYY') &'<='& Date(max(Date),'DD/MM/YYYY')

attached application for your reference purpose

thanks

sasi

11 Replies
sasikanth
Valued Contributor III

Re: Filter Months

HI,

create 2 expressions 1 for 12 months & another for 6 Months then enable them using a variable

For last 12 Months:

sum({<Date={ ">=$(=Date(addMonths(max(Date),-12),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),-12),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

For Last 6 months:

sum({<Date={ ">=$(=Date(addMonths(max(Date),-6),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),-6),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

please find the attached application

surajap123
Contributor II

Re: Filter Months

Hi Sasi,

Thanks for the solution.

Could you help me with a single expression, instead of conditionally enabling/disabling the expressions.

Thanks!

sasikanth
Valued Contributor III

Re: Filter Months

HI,

Create a variable to replace -12/-6

sum({<Date={ ">=$(=Date(addMonths(max(Date), $(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),$(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

Set button trigger as 

for Last 12 months :  vlimit=-12

For Last 6 Month  : Vlimit=-6

surajap123
Contributor II

Re: Filter Months

Hi Sasi,

I notice that the expression is showing 1 additional month.

eg- If i select 6 months button i see 7 months.

Do u think, if i change -6 to -5, is the correct approach.

sasikanth
Valued Contributor III

Re: Filter Months

hi,

Yes , you can change it to -5  or   change '>=' to '>'

surajap123
Contributor II

Re: Filter Months

Thanks for the reply. Sorry to bother again.

I realised that my requirement not just to filter locally in the chart, but in the whole dashboard.

So when user click on 12 months button, the selection has to happen on MonthYear field, so that the whole dashboard is reflected.

I hope this can be achieved by putting the set analysis expression in the button and keeping the chart expression unchanged. ie. sum(Margin)/sum(Sales)

I tried to put the below expression in the button using SelectInField trigger, but it is not working.

>=$(=Date(addMonths(max(Date), $(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))


Please help!

satheshreddy
Contributor III

Re: Filter Months

Hi Suraj,

you can use addmonths() and write the exp you will get it.

Regards

Sathish

surajap123
Contributor II

Re: Filter Months

I have written the below expression in a button(to select last 6 months) with action Select In Field-

MonthYear

='>='$(=Date(addMonths(max(Date), -5),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))

But his is not working. !


Please see bottom chart in the app


Highlighted
Partner
Partner

Re: Filter Months

Hi Suraj,

Please use the attached application.

I have created a trigger . Go to Document Properties-> Triggers-> On Open, which will show the chart with no default condition.

For other i have used the 6 Month and 12 Months Text object.

Please check and confirm