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: 
vinni
Contributor
Contributor

Y'Day,LastWeek, MTD,QTD,QTD-1,QTD-2,QTD-3,YTD,LYTD In Filter Pane

Hi,

Any one please help me,

I have only date field, but i want to show all  Y'Day,LastWeek, MTD,QTD,QTD-1,QTD-2,QTD-3,YTD,LYTD in filter pane.

Expected :

Dates
Yesterday
LastWeek
MTD
QTD
CurrentQTD-1
CurrentQTD-2
CurrentQTD-3
YTD
LastYear
Last2Years

I have used below calculation in script level but it was giving only 

clipboard_image_0.png

Yesterday
LastWeek
MTD       in filter pane

If((Num(CASE_CREATEDDATE)= Num(Today()-1)),'Yesterday',
If((Num(CASE_CREATEDDATE)>= Num(Weekstart(Today()-7)) and Num(CASE_CREATEDDATE)<= Num(Weekend(Today()-7))),'LastWeek',
If((Num(CASE_CREATEDDATE)>= Num(MonthStart(Today())) and Num(CASE_CREATEDDATE)<= Num(MonthEnd(Today()))),'MTD',
If((Num(CASE_CREATEDDATE)>= Num(QuarterStart(Today())) and Num(CASE_CREATEDDATE)<= Num(QuarterEnd(Today()))),'QTD',
If((Num(CASE_CREATEDDATE)>= Num(QuarterStart(AddMonths(Today(),-3))) and Num(CASE_CREATEDDATE)<= Num(QuarterEnd(AddMonths(Today(),-3)))),'Current QTD-1',
If((Num(CASE_CREATEDDATE)>= Num(QuarterStart(AddMonths(Today(),-6))) and Num(CASE_CREATEDDATE)<= Num(QuarterEnd(AddMonths(Today(),-6)))),'Current QTD-2',
If((Num(CASE_CREATEDDATE)>= Num(QuarterStart(AddMonths(Today(),-9))) and Num(CASE_CREATEDDATE)<= Num(QuarterEnd(AddMonths(Today(),-9)))),'Current QTD-3',
If((Num(CASE_CREATEDDATE)>= Num(YearStart(today())) and Num(CASE_CREATEDDATE)<= Num(MonthEnd(Today()))),'YTD',
If((Num(CASE_CREATEDDATE)>= Num(YearStart(AddMonths(today(),-12))) and Num(CASE_CREATEDDATE)<= Num(MonthEnd(AddMonths(Today(),-12)))),'Last Year',
If((Num(CASE_CREATEDDATE)>= Num(YearStart(AddMonths(today(),-24))) and Num(CASE_CREATEDDATE)<= Num(MonthEnd(AddMonths(Today(),-24)))),'Last 2 Years'
)))))))))
) as CASE_CREATEDDATE_New

 

Please suggest me if any alternative method to achieve this requirement..

 

Thanks in advance..😊

 

 

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

Not sure what is going wrong but that depends on the data.

But I dont believe you can do it this way with a single column. because date would fall into multiple categories not just one. e.g. Yesterdays date should also be in YTD . but if you do it like this approach it will included in just category.

In my opinion you will need to an approach like below. 

1- The selection will be an island table

2-  The measure you need create in such a way that a different set analysis is applied on each selection

you can either do full set analysis with date condition or create separate flags for each condition

e.g. if((Num(CASE_CREATEDDATE)= Num(Today()-1),1,0) as YesterdayFlag

, if(Year(CASE_CREATEDDATE)=Year(today),1,0) as YTDFlag

 

vinni
Contributor
Contributor
Author

Hi Dilipranjith,

Thanks for your reply ,

You approach is correct,but i need all in single fields,if i get all in single field then i will use that field in filter pane.  

I need all the Y'Day,LastWeek, MTD,QTD,QTD-1,QTD-2,QTD-3,YTD,LYTD  in a single column...

 

Please let me know if any other alternative to achieve this requirement...

Thanks.

dplr-rn
Partner - Master III
Partner - Master III

Why do you need in single column? Single column will notr work imo. Look at the example I gave.

Repeatingit again take E.g. of one date row 26 Sept 2019. Your 1 column should ideally have at least these values  in the flag in a single "Yesterday", MTD, YTD , which is not possible.

 

As far I understand you need it to filter the data right. Hence my approach.