Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..😊
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
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.
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.