Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator II
Creator 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
Master
Master

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

View solution in original post

11 Replies
sasikanth
Master
Master

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
Creator II
Creator II
Author

Hi Sasi,

Thanks for the solution.

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

Thanks!

sasikanth
Master
Master

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
Creator II
Creator II
Author

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
Master
Master

hi,

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

surajap123
Creator II
Creator II
Author

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
Creator III
Creator III

Hi Suraj,

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

Regards

Sathish

surajap123
Creator II
Creator II
Author

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


kamalqlik
Partner - Specialist
Partner - Specialist

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