Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
vinni
New 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
Partner
Partner

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

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
New Contributor

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

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.

Partner
Partner

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

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.