Charts will dynamically show the previous & previous of previous dates from the selected date with the selected date using Set Expression.

[Ravi_Shankar_M]

Hello,

I have below scenario to implement on a Line chart with dates as dimension with counts of values as measures, can anyone help me do this?

By default, user will see Max date when he comes to the Sheet., Else we will have below conditions to be addressed.

if {
User Selected date = Max date
Then
Max Date / Max Date -1 / Max Date -2 / Max Date -3
}
Else-if{
User doesn't select any dates,
Then
Max date / Max Date -1 / Max Date -2 / Max Date -3
}
Else-if{
User selects a date
Then
Selected_Date / Selected_Date -1 / Selected_Date-2 / Selected_Date-3
}
Else-if{
User selects a set of dates then populate those days
}

[Kushal_Chawda]

@Ravi_Shankar_M  create two variables on front end as following

vStartDate = date(if(GetSelectedCount(Date)>1, min(Date),max(Date)-3))

vEndDate = max(Date)

You can now use these variables in the set analysis measure like below

Sum({<Date={">=$(vStartDate)<=$(vEndDate)"}>}Sales)

[Ravi_Shankar_M]

Hi @Kushal_Chawda,

Thanks for your solution,
To note: It's a weekly data, I updated the formula as below:

Sample Data: 
Calc_Date = 2024/05/20-2024/05/26
START_DT = 2024/05/20
END_DT = 2024/05/26

Created Dim while Loading:
Max_date = MAX(Date(Date#(END_DT, 'YYYY/MM/DD'), 'YYYY/MM/DD'))
Min_date = MIN(Date(Date#(END_DT, 'YYYY/MM/DD'), 'YYYY/MM/DD'))

Created Variable:
vStartDate = date(if(GetSelectedCount(START_DT)>1, Min_date,DATE(Max_date-7)),'YYYY/MM/DD')

& 
vEndDate = MAX(Date(Date#(END_DT, 'YYYY/MM/DD'), 'YYYY/MM/DD'))

and

Dimension:
if(TYPE = 'X',if(GetSelectedCount(Calc_Date) >= 0 ,START_DT))

Measure :
if(GetSelectedCount(Calc_Date) > 0, Sum({<START_DT={">=$(vStartDate)<=$(vEndDate)"}>}[TOTAL])))

If i select any date, It didn't worked.

But this one worked if I didn't select any date: 
if(GetSelectedCount(Calc_Date) = 0 ,sum( {< M_D_Y_Cal_Date = {">=$(=Date(Max_date -21))<=$(=Date(Max_date ))"} >} [TOTAL]))

M_D_Y_Cal_Date is just the format for MM/DD/YYYY of START_DT

[Kushal_Chawda]

@Ravi_Shankar_M  It seems there are multiple dates involved. Could you share example of how would you like to show dates?

[Ravi_Shankar_M]

Here is the sample data:

Start_dt	end_dt	Period_name	TOTALCOUNTS	LOADED_COUNTS	ERRORED_COUNTS
2024-05-01	2024-05-31	5 2024 Monthly Calendar	630	624	6
2024-04-22	2024-04-28	17 2024 Weekly Calendar	13	13	0
2024-05-06	2024-05-12	19 2024 Weekly Calendar	418	418	0
2024-02-01	2024-02-29	2 2024 Monthly Calendar	4	0	4
2024-05-01	2024-05-31	5 2024 Monthly Calendar	2340	2192	148
2024-05-16	2024-05-31	10 2024 Semi-Monthly Calendar	223	222	1
2021-09-01	2021-09-30	9 2021 Monthly Calendar	1	0	1
2020-01-01	2020-01-31	1 2020 Monthly Calendar	1	0	1
2021-10-01	2021-10-31	10 2021 Monthly Calendar	1	0	1
2024-04-01	2024-04-30	4 2024 Monthly Calendar	4	4	0

[Ravi_Shankar_M · Accepted solution]

Guess What ! I found the solution myself. Using set-exp:

I have 2 conditions vmeasure = 1 is Count of records & 2 is Count of amount.

Calendar Date is derived from Start_dt.

pick(vmeasure,
if(GetSelectedCount(Calendar_Date) = 0 ,
SUM({<TYPE = P({<TYPE = {'A'}>} )>} (TOTAL_COUNTS)),
if(GetSelectedCount(Calendar_Date) > 0 ,
SUM({$<Calendar_Date = {">=$(=MAX(Date(Date#(Start_dt, 'YYYY/MM/DD')-35,'YYYY/MM/DD')))<=$(=MAX(Date(Date#(Start_dt, 'YYYY/MM/DD'),'YYYY/MM/DD')))"}>} (TOTAL_COUNTS))
))
,

if(GetSelectedCount(Calendar_Date) = 0 ,
SUM({<TYPE = P({<TYPE = {'A'}>} )>} ([TOTAL_$s])),
if(GetSelectedCount(Calendar_Date) > 0 ,
sum({<Calendar_Date = {">=$(=MAX(Date(Date#(Start_dt, 'YYYY/MM/DD')-35,'YYYY/MM/DD')))<=$(=MAX(Date(Date#(Start_dt, 'YYYY/MM/DD'),'YYYY/MM/DD')))"}>} [TOTAL_$s])
)))

To be more precise:
If User selects a date it will pull the selected date with previous 3 dates from the selected date, also if user doesn't select any date this will give you the latest 3 date data.


if(GetSelectedCount(Filter_Date) = 0 ,
SUM({<TYPE = P({<TYPE = {'A'}>} )>} ([TOTAL_$s])),
if(GetSelectedCount(Filter_Date) > 0 ,
sum({<Calendar_Date = {">=$(=MAX(Date(Date#(Your_Date, 'YYYY/MM/DD')-35,'YYYY/MM/DD')))<=$(=MAX(Date(Date#(Your_Date, 'YYYY/MM/DD'),'YYYY/MM/DD')))"}>} [TOTAL_$s])
)))

Note: Set-expression will only work if you use with aggregation to it.

Cheers!
Ravi

[seanbruton]

Superb, helpful solution!!!