topic Re: Filter Months in QlikView

Filter Months

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Filter Months

sasikanth — Fri, 05 Jan 2018 11:55:01 GMT

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

Re: Filter Months

surajap123 — Fri, 05 Jan 2018 12:57:28 GMT

Hi Sasi,

Thanks for the solution.

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

Thanks!

Re: Filter Months

sasikanth — Fri, 05 Jan 2018 15:03:09 GMT

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

Re: Filter Months

surajap123 — Mon, 08 Jan 2018 05:28:43 GMT

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.

Re: Filter Months

sasikanth — Mon, 08 Jan 2018 06:30:07 GMT

hi,

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

Re: Filter Months

surajap123 — Mon, 08 Jan 2018 06:50:56 GMT

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!

Re: Filter Months

satheshreddy — Mon, 08 Jan 2018 07:13:55 GMT

Hi Suraj,

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

Regards

Sathish

Re: Filter Months

surajap123 — Mon, 08 Jan 2018 07:36:44 GMT

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

Re: Filter Months

kamalqlik — Mon, 08 Jan 2018 08:20:58 GMT

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

Re: Filter Months

sasikanth — Mon, 08 Jan 2018 08:21:54 GMT

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

Re: Filter Months

surajap123 — Mon, 08 Jan 2018 09:02:05 GMT

Thanks for your help. you are awesome.