Hi All,
I have a table that is like
null
I would like to write a logic - when a case ID has both 'CLOSED' and 'OPEN' choose 'CLOSED' and when a caseID has only 'OPEN' then choose open.
I tried this -
if [New_Val] = 'OPEN' and [New_Val] = 'CLOSED' THEN 1 ELSE 0 END
even I fixed it against Case ID but did not get it working.
Any advice will be appreciated.
regards,
Rishi
Hi All,
Pardon for not posting the attachment properly. I have attached the png file.
So the situation that I have is that each of the New_Val has different timestamps for each CaseID.
The most recent timestamp is either 'OPEN' or 'CLOSED'. The 'OPEN' timestamps can be a couple of weeks old than todays date.
I wanted to show the total time needed for a case to get started and then either get 'CLOSED' or if it is 'OPEN' take the last date as todays date.
I used the following LOD -
CASE [New_Val]
when 'CLOSED' THEN { FIXED [CASEID],[TIERS]: DATEDIFF('month', [Min case creation time ], [Max Case Creation Time ])}
when { FIXED [CASEID]: MIN( [New_Val] ) } then { FIXED [CASEID],[TIERS]: DATEDIFF('month', [Min case creation time ], TODAY())}
END
where [Max Case Creation Time] is max(timestamp) and Min case creation time is min(timestamp) .
I took { FIXED [CASEID]: MIN( [New_Val] ) } with help from Swaroop.
However I am not getting the result I want as value for INTERVIEW is being displayed in place of OPEN AS 'I' comes before 'O'
.
