Can i get some guidance on how to get the table as per image below?
I just want the table to show the Current and Previous Substasks in 1 line instead of showing everything.
I think I got it but it’s a lot!
For for starters I am using client_id in place of status (WLS=‘open’ , AWS=‘closed’). I am using cognito_id in place of substack. And I am using event_timestamp instead of date.
First get the last substack if it is open (substitute client_id[users] for how you determine if it is open):
last value open= lastValue({cognito_id},[ifelse({client_id[users]}=‘WLS’,1,0) ASC,{event_timestamp} ASC])
Then get the last substack if it is closed (substitute client_id[users] for how you determine if it is closed):
last value closed = lastValue({cognito_id},[ifelse({client_id[users]}=‘AWS’,1,0) ASC,{event_timestamp} ASC])
Then get the latest date of these last values:
last date open = lastValue({event_timestamp},[ifelse({client_id[users]}=‘WLS’,1,0) ASC,{event_timestamp} ASC])
last date closed = lastValue({event_timestamp},[ifelse({client_id[users]}=‘AWS’,1,0) ASC,{event_timestamp} ASC])
Then filter your sheet to be only dates that are a part of these last dates.
last date equal = ifelse(toString(max({event_timestamp}))=toString({last date open}) OR toString(max({event_timestamp}))=toString({last date closed}),‘Yes’,‘No’)
Next we need to grab the closed / resolved id’s and bring them onto the same row as the open ones. So we are going to make two calculated fields with a lead function.
lead id = lead({cognito_id},[{event_timestamp} DESC],1,[{last date equal}])
lead eventtimestamp = lead({event_timestamp},[{event_timestamp} DESC],1,[{last date equal}])
We are now going to filter the table so that lead id does not equal NULL:
Finally we are going to get the current substack age by taking the difference of the current event timestamp (last date open) and the lead eventtimstamp (last date closed).
date diff = dateDiff(max({event_timestamp}),{lead eventtimestamp},‘DD’)
You table should now be able to use the original columns and for the last three use this: coginito_id,lead_id, date diff
Let me know if you get tripped up on any of these.
Hope this helps!
1 Like
Max, I think you are using calculations within QS itself. Am I correct? My approach, because my company uses redshift under QuickSight, would to be put a lot of that logic into the SQL query itself. Agreed, it’s a non-trivial task either way. And we don’t know his datasource!
Yes, this is all in QS.
I would definitely recommend doing it at the SQL level too!
2 Likes