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

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

vinni — Sat, 16 Nov 2024 20:00:15 GMT

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

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..😊

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

dplr-rn — Thu, 26 Sep 2019 16:15:58 GMT

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

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

vinni — Fri, 27 Sep 2019 03:16:18 GMT

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.

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

dplr-rn — Fri, 27 Sep 2019 10:37:47 GMT

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.