Advanced filter

[roshnirao]

Hi there,

I'm new to Qlik Sense. I am trying to use advanced filter on date column.Please can you help me!

What I trying to achieve is, select CONTROL_MONTH in one sheet, show previous three months data based in sheet 2 based on the month selected in sheet one. 

If Sep-2019 is selected, next sheet should show data for Sep-2019, Aug-2019, Jul-2019. If Aug-2019 is selected, next sheet should show data for  Aug-2019, Jul-2019, Jun-2019, so on.

Data set:

CONTROL_MONTH	CATEGORY	KEYLINK	STATUS
Sep-19	EXCEPTION	ABC1	ACTIVE
Aug-19	EXCEPTION	ABC1	ACTIVE
Jul-19	HAPPY PATH	ABC1	INACTIVE

Sheet 1: CONTROL MONTH is FILTER

On filtering Sep-19, below data is shown in table

CONTROL_MONTH	CATEGORY	KEYLINK	STATUS
Sep-19	EXCEPTION	ABC1	ACTIVE

Sheet 2: I want to display the previous three months data for the KEYLINK in exception for current month (Sep-19)

CONTROL_MONTH	KEYLINK	STATUS
Sep-19	ABC1	ACTIVE
Aug-19	ABC1	ACTIVE
Jul-19	ABC1	INACTIVE

How do I set the filter in sheet sheet 2, so I can get two values selected for one value selected in sheet 1?

PS: I have tried these in filter panel, data>Dimension>Field, but is not working.

=Sum({<Date={">=$(=Floor(MonthStart(Today(),-1)))<=$(=Floor(MonthEnd(Today(),-1)))"}>}[CONTROL_MONTH.autoCalendar.YearMonth])

[nisha_rai · Accepted solution]

Hi,

First you need to create the control  date from Control_Month , then apply the below condition in expression

sum({<ControlDate={"<=$(=Date($(=Max(ControlDate)))) >= $(=Addmonths($(=Max(ControlDate)),-2))"}>}1)

Hope so it will work.

[roshnirao]

Thank you for responding Nisha.

I have tried like this - 

=sum({<CONTROL_MONTH.autoCalendar.Date={"<=$(=Date($(=Max(CONTROL_MONTH.autoCalendar.Date)))) >= $(=Addmonths($(=Max(CONTROL_MONTH.autoCalendar.Date)),-2))"}>}1)

It is not working. It shows as invalid dimension. ! I'm passing this value in fields column of filter pane. Am I doing it right?

CONTROL_MONTH column has values like this - '2019-09-01 00:00:00.000000',  '2019-08-01 00:00:00.000000', '2019-07-01 00:00:00.000000', '2019-06-01 00:00:00.000000', '2019-09-05 00:00:00.000000'.

[nisha_rai]

Hi, 

You can create date field in the script , using the below code.

Month_Mapping:
Mapping
LOAD * INLINE [
Month,FileMonNum
Jan,1
Feb,2
Mar,3
Apr,4
May,5
Jun,6
Jul,7
Aug,8
Sep,9
Oct,10
Nov,11
Dec,12
];
Test:
Load * Inline [
CONTROL_MONTH, KEYLINK, STATUS
Sep-19, ABC1,ACTIVE
Aug-19,ABC1, ACTIVE
Jul-19, ABC1, INACTIVE
];

NoConcatenate
Test1:
Load *,
Date#( Makedate('20'&right(CONTROL_MONTH,2) ,ApplyMap('Month_Mapping',trim(left(CONTROL_MONTH,3))))) As ControlDate

Resident Test;

Drop Tables Test;

May it helps

[roshnirao]

It is still not working Nisha!

[nisha_rai]

Can you share the error or screen shot what you get?

[roshnirao]

It worked for me.

I have used button actions with select a filed, and have passed below expression as you have suggested.

= '(' & Concat(DISTINCT {<[CONTROL_MON]={"<=$(=Date(Max([CONTROL_MON]))) >=$(=AddMonths(Date(Max([CONTROL_MON])), -2))"}>} Chr(34) & [CONTROL_MON] & Chr(34) ,'|') & ')'

The important thing is to keep the date in DD/MM/YYYY format. Only then this will work. I have been trying on this with DD-MON-YY format. Thus it wasn't working.