Getting value from previous record or getting last value for a partition

Q&A
,
1

Hi,
I have a dataset with three fields (along with lot of other fields) : id, created, status.
Status can have two values for a particular id: error, success.
There could be multiple records for a single id with status of error or success.
I need to know if there is a success status for an id.
Example : If an id has 1 or more errors and finally 1 success, I need to count this as 1 successful_retry.
If an id has 1 success with no previous errors, it is not counted towards a successful_retry, but counted once towards successful_first_time_itself.
If an id has 2 errors with no success, it is counted twice towards error_count.

In my analysis, I am trying to create a calculated field with below expression and trying to show it in a table along with other fields.
lastValue({status},[{created date}],[{id}])

This is not working, as it seems that this is an aggregated field but not a field at individual record
level.

I also tried calculated field with below expression, thinking it would give me the value at individual record level, so I can compare status value with previous record’s status value. However, this also seems to be an aggregated field.
lag({status},[{created} ASC],1,[{id}])

If I setup similar fields directly in my dataset custom SQL using window functions, it works just fine. Why do they not work at the analysis level? Is there a way to have window function equivalents at the analysis level? I do not want to keep modifying my dataset custom sql whenever a new analysis is needed that mandates window functions.

Any help is appreciated.
Thanks in advance.

1 Like
2

Hello @kborra, welcome to the Quick Sight community!

So when you say the last value option is not working, what exactly is happening when you implement this? I am wondering if utilizing something like a countOver would be more beneficial for something like this. Using that function, with a nested ifelse statement, could give you the number of error and success statuses per ID. I will show you an example below:

Errors per ID = countOver(ifelse({status} = 'error', {status}, NULL), [{id}], PRE_AGG)
Successes per ID = countOver(ifelse({status} = 'success', {status}, NULL), [{id}], PRE_AGG)

Then you could use it to compare with the number of times one or the other appeared, or the total number of tries by ID since that is used within the partition field. Let me know if this helps!

3

Hello @kborra, since we have not heard back from you, I wanted to check in and see if my previous response helped guide you towards your expected output. Please let me know if you have any remaining questions, or mark my previous response as the solution if it resolved your issue. If we do not hear back from you in 3 days, I will archive this topic. Thank you!

4

Dylan,
Thank you very much for the suggestion. It worked for my requirement perfectly.
Sorry I could not reply sooner.
Thanks again.

1 Like
5

Hello @kborra, thank you for following up and I am glad the solution worked for your use case!