Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate difference value between two values in one column based on date and assignment

Hi all,

 

I want to create a calculated column named 'project' to come true the picture shown as below,

The 'project' value is 'NEW_VALUE' based on 'ESN' and 'FIELD' value equals 'A'.

And between two 'FIELD' values  euals 'A' based on 'CREATED_TS', the 'project' value euqals the older 'NEW_VALUE' value. 

 

 

Sample data as below:

 

CREATED_TSFIELDNEW_VALUEESN
2019/7/25 5:04AReg_KI10001.01_07251
2019/7/25 5:20AReg_KI10001.01_07241
2019/7/25 5:29AReg_KI10001.01_07231
2019/8/6 4:27AReg_KI10001.01_08051
2019/7/25 5:06AReg_KK10001.00_07252
2019/8/22 6:41AReg_KK10001.02_08222
2019/9/1 9:39Belse12
2019/9/1 9:45Belse22
2019/9/2 12:34Belse52
2019/9/2 12:34Belse42
2019/9/2 12:45Belse32
2019/9/2 13:15Belse62
2019/9/4 13:15AReg_SJ10001.02_08232
2019/9/4 14:25AReg_GI10001.02_08212


Please help me for this issue!

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    Check if this works:

    project = 
VAR v_lag = CALCULATE( MAX( Data[NEW_VALUE] ), FILTER( Data, Data[CREATED_TS] <= EARLIER( Data[CREATED_TS] ) ) )
RETURN
IF( Data[FIELD] == "A", Data[NEW_VALUE], v_lag )

    Here's what i got:

     

    Thanks.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    Check if this works:

    project = 
VAR v_lag = CALCULATE( MAX( Data[NEW_VALUE] ), FILTER( Data, Data[CREATED_TS] <= EARLIER( Data[CREATED_TS] ) ) )
RETURN
IF( Data[FIELD] == "A", Data[NEW_VALUE], v_lag )

    Here's what i got:

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      I think "MAX( DATA[NEW_VALUE] )" does not works if "NEW_VALUE" like picture as below.