topic Re: Creating a button to select Past one month in QlikView

Creating a button to select Past one month

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

Hi Everyone,

I'm trying to create a button for selecting last past one month data.

For this i have created a button and added two actions to Set Variable where one variable name is vStartDate and other one is vEndDate for vStartDate i have given a value as =Monthstart(Max({1}[Activity Creation Date]),-1) and for vEndDate =MonthEnd(Max({1}[Activity Creation Date]),-1) this is working fine.

Like changing the vStartDate and vEndDate to past one month dates.

Now, I want on clicking this button it has to apply filter can any one help me with the Select field action The Field i'm using is Activity Creation Date.

Thanks Inadvance.

Re: Creating a button to select Past one month

jwjackso — Wed, 26 Aug 2020 15:47:39 GMT

If you want to set the [Activity Creation Date] for all visualization in the application, it would be easier to use the "Select In Field" action of the button. The field will be [Activity Creation Date] and the "Search String" will be

='('&Chr(34)&Concat({<[Activity Creation Date]={">=$(=MonthStart(Max({1}[Activity Creation Date]),-1))<=$(=MonthEnd(Max({1}[Activity Creation Date]),-1))"}>}[Activity Creation Date],Chr(34)&'|'&Chr(34))&Chr(34)&')'

The "Search String" is going to look like '("7/1/2020"|"7/2/2020"|....|"7/31/2020")'

The pipe symbol is a logical "OR" operator

If you use the button to set 2 variables, you will need to use Set Analysis and add the variable to each calculation.

Re: Creating a button to select Past one month

TheBeginner — Wed, 26 Aug 2020 15:54:35 GMT

Hi @jwjackso ,

Firstly thanks for reply,

Actually it is not working my date format is 'DD-MMM-YYYY' could you please help me out.

Thanks In advance

Re: Creating a button to select Past one month

jwjackso — Wed, 26 Aug 2020 16:04:37 GMT

Add a Date function to format the date field correctly

='('&Chr(34)&Concat({<[Activity Creation Date]={">=$(=Date(MonthStart(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY'))<=$(=Date(MonthEnd(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY'))"}>}[Activity Creation Date],Chr(34)&'|'&Chr(34))&Chr(34)&')'

Re: Creating a button to select Past one month

TheBeginner — Wed, 26 Aug 2020 16:12:48 GMT

@jwjackso Thank you so much it is working, But in current selections showing like this

But i want to show the range instead like

Could you please help me out.

Thanks in advance

Re: Creating a button to select Past one month

jwjackso — Wed, 26 Aug 2020 16:29:25 GMT

Add a "Distinct" to display the dates only once. I'll see if it is possible to format the date they you want.

='('&Chr(34)&Concat({<[Activity Creation Date]={">=$(=Date(MonthStart(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY'))<=$(=Date(MonthEnd(Max({1} [Activity Creation Date]),-1),'DD-MMM-YYYY'))"}>} Distinct [Activity Creation Date],Chr(34)&'|'&Chr(34))&Chr(34)&')'

Re: Creating a button to select Past one month

TheBeginner — Wed, 26 Aug 2020 16:35:38 GMT

Again Thanks @jwjackso I worked.

It is showing distinct values

But my requirement is to show like

Could you please help me out

Thanks in advance

Re: Creating a button to select Past one month

jwjackso — Wed, 26 Aug 2020 16:43:22 GMT

This "Search String" looks like the format you want:

='(>='&Date(MonthStart(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY')&'<='&Date(MonthEnd(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY')&')'

Re: Creating a button to select Past one month

TheBeginner — Wed, 26 Aug 2020 16:48:50 GMT

@jwjackso Thank you so much. Finally it worked but last one thing is there any possibilities to remove brackets ()

I mean currently it was like this

Can we have it like

Thanks in advance

Re: Creating a button to select Past one month

jwjackso — Wed, 26 Aug 2020 16:57:51 GMT

='>='&Date(MonthStart(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY')&'<='&Date(MonthEnd(Max({1}[Activity Creation Date]),-1),'DD-MMM-YYYY')

Re: Creating a button to select Past one month

TheBeginner — Wed, 26 Aug 2020 17:02:42 GMT

@jwjackso Thank you. It worked 👍