Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a flied RealTime. RealTime is valuable in every day.
I want the RealTime value of the last day minus the previous day in the 'Realyesterday' column.
EX MM/DD/YYYY 01/02/2023
on 01/03/2023
Or can I do something else that has the same result?
i would solve this using a bridge that links your date with the prior date. the bridge connects your calendar with your data.
when a date in the calendar is selected, it will link to the current date and the prior date at the same time. so for 1/2/2023 it links to both 1/2/2023 (CURRENT) and 1/1/2023 (PRIORDATE), this way you can trace back to the original data and get Real time for current and PRIOR dates. here is a sample code:
RealTime:
//notice Date is renamed as you want the user to use the Calendar
//the KEY field will be used to link the bridge with data
load Number, RealTime,Date as CurrentDate, Date&'|'&Number as Key inline [
Date,Number, RealTime
1/2/2023,SN123,6641042
1/2/2023,SN124,664104
1/2/2023,SN125,66410
1/2/2023,SN126,6641
1/1/2023,SN123,774104
1/1/2023,SN124,77410
1/1/2023,SN125,7741
1/1/2023,SN126,774
];
//this just builds the calendar
NoConcatenate
Calendar:
load CurrentDate as Date Resident RealTime;
//this links the calendar to the current date
NoConcatenate
Bridge:
load CurrentDate as Date, Number, date(CurrentDate-1) as PriorDate, Key, 'CURRENT' as DataType Resident RealTime;
//this starts the build for the prior date
NoConcatenate
tmp:
load Date, Number, PriorDate Resident Bridge;
//this gets the KEY to the PRIOR date
//so TMP actually pints to the KEY for the prior date
inner join (tmp)
load CurrentDate as PriorDate, Number, Key Resident RealTime;
//add it to the BRIDGE
concatenate(Bridge)
load
Date, Number, PriorDate, Key, 'PRIORDATE' as DataType Resident tmp;
//at this point, for a DATE in teh calendar,
//the BRIDGE points to the CURRENT DATE where DATE TYPE =CURRENT
//at the same time points to te PRIOR DATE where DATE TYPE =PRIORDATE
//this cleans up unwanted entities
drop table tmp;
drop field Number from Bridge; since youve built the logic that associates current to prio date in your DM, the expressions become simpler:
to get the realtime for current date:
=sum({<DataType={'CURRENT'}>}RealTime)
and
=sum({<DataType={'PRIORDATE'}>}RealTime)
difference will obviously be
=sum({<DataType={'CURRENT'}>}RealTime)-sum({<DataType={'PRIORDATE'}>}RealTime)
this one is a version that determines the latest date as CURRENT:
RealTime:
load Number, RealTime,Date as CurrentDate, Date&'|'&Number as Key inline [
Date,Number, RealTime
1/2/2023,SN123,6641042
1/2/2023,SN124,664104
1/2/2023,SN125,66410
1/2/2023,SN126,6641
1/1/2023,SN123,774104
1/1/2023,SN124,77410
1/1/2023,SN125,7741
1/1/2023,SN126,774
12/31/2022,SN123,774104
12/31/2022,SN124,77410
12/31/2022,SN125,7741
12/31/2022,SN126,774
];
NoConcatenate
Calendar:
load CurrentDate as Date Resident RealTime;
NoConcatenate
tmp:
load Number, Date(max(CurrentDate)) as CurrentDate Resident RealTime group by Number;
inner join (tmp)
load Number, CurrentDate, Key Resident RealTime;
NoConcatenate
Bridge:
load CurrentDate, Number, date(CurrentDate-1) as PriorDate, Key, 'CURRENT' as DataType Resident tmp;
drop table tmp;
NoConcatenate
tmp:
load Date, Number, PriorDate Resident Bridge;
inner join (tmp)
load CurrentDate as PriorDate, Number, Key Resident RealTime;
concatenate(Bridge)
load
Date, Number, PriorDate, Key, 'PRIORDATE' as DataType Resident tmp;
drop table tmp;
drop field Number from Bridge;
this is how the data looks like:
this is the report:
@Sxbbb wrote:
I have a flied RealTime. RealTime is valuable in every day.I want the RealTime value of the last day minus the previous day in the 'Realyesterday' column.EX MM/DD/YYYY 01/02/2023
on 01/03/2023
Or can I do something else that has the same result?
haha what u said is correct , data is available everyday and update every day.
