Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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.
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.
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'.
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
It is still not working Nisha!
Can you share the error or screen shot what you get?
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.