topic Re: Charts will dynamically show the previous & previous of previous dates from the selected date with the selected date using Set Expression. in Management & Governance

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

Ravi_Shankar_M — Thu, 17 Oct 2024 13:18:11 GMT

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
}

Re: Dynamic change of dimension based on user selection.

Kushal_Chawda — Tue, 15 Oct 2024 15:14:20 GMT

@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)

Re: Dynamic change of dimension based on user selection.

Ravi_Shankar_M — Tue, 15 Oct 2024 17:08:08 GMT

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

Re: Dynamic change of dimension based on user selection.

Kushal_Chawda — Tue, 15 Oct 2024 18:57:11 GMT

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

Re: Dynamic change of dimension based on user selection.

Ravi_Shankar_M — Wed, 16 Oct 2024 07:07:57 GMT

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

Re: Dynamic change of dimension based on user selection.

Ravi_Shankar_M — Thu, 17 Oct 2024 13:23:55 GMT

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

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

seanbruton — Thu, 17 Oct 2024 15:01:03 GMT

Superb, helpful solution!!!