Find the previous value of a specific column

Question & Answer
,
1

Good afternoon,
I have a simple data set with these column

AlarmCode,
Description,
DoStart,
DoEnd,
FatherAlarmCode

I’m trying to create a calculated field to find the previous “FatherAlarm” DoEnd data. My goal is this

image
image763×189 17.5 KB

I’m trying with the lag function, but I’m not able to have the previous DoEnd of a specific “AlarmCode” condition. How can I do it?

Best regards
Enrico

2

I think you need to partition by FatherAlarmCode.

lag
(
     {DoEnd}
     ,[ DoEnd]  
     ,1,
     ,[ {FatherAlarmCode} ] 
)
1 Like
3

Already tried, without any luck. This is the result of the lag function

image
image780×231 20.5 KB

image

I tryed Lag and Lead, with ASC or DESC, but I never get what I need

4

@egobbo try defining a transform rule for Nulls in FatherAlarmCode to get the desired result. Your sample code would look like this…

lag({DoEnd}, [ DoEnd] ,1,[ nullif({FatherAlarmCode},AlarmCode),{FatherAlarmCode} ] )

1 Like
5

Great suggestion!

To get what I need, I created one more additional calculated field

DoStartPart → formatDate({DoStart}, ‘dd/MM/yyyy’)

then I changed my PrevFatherDoEnd in this way

firstValue(DoEnd, [ DoStart ASC ],[ifelse(isNull(FatherAlarmCode),AlarmCode,FatherAlarmCode),DoStartPart ])

in this way the result is this

image
image820×387 52.5 KB

that is what I was looking for!

1 Like