Tracking Metrics Before and After Change Point

[nnguyen95]

My team and I are currently working on a pivot table where we track how slot machines are performing based on their Stand ID (denoting a location on a floor), based on changes made to machines at that location.

The "ChangesPivot_v1" attachment shows our first attempt at this, where the Stand Config Version number changes every time any change is made to the machine. The version number is based on a start and end date for that particular configuration. Metrics and changes between configurations are expressed in multiple rows, one for each machine configuration.

To highlight a specific example, we would consider a change to have occurred on 3/10/2023 in the "ChangesPivot_v1" attachment. Prior to the change on 3/10, this machine recorded $2,199.14 in Win. After the change, from 3/10-3/13, this machine recorded $1,578.11 in Win.

The request was made to consolidate these and present metrics broken up into before and after a particular change is made. Our attempt at this is shown in the "ChangesPivot_v2" attachment. We attempted to display the Win recorded for a given Configuration (denoted by the start date), then the Win recorded for the previous configuration.

To accomplish this, we tried using the PREV function in the widget editor (please see "Prev_logic" attachment). However, this is not giving us the results that we expect. In the v2 screenshot, for the change on 3/10/2023 we would want the Win column to show $1,578 and Previous Win column to show $2199 ideally on the same row, but we're not getting the results we would like.

Is there potentially a better way to go about accomplishing this? Please advise if any clarification will be necessary.

Thank you

[Liliia_DevX]

Hello!

Thanks for sharing your use-case. Since the values you need to display are not connected to sequential dates, the PREV function will not work here. This function with the syntax you created could take only the previous day for the specific date and not the most recent day within the row.

In your case, it looks like you need to choose the value for the most recent date available for a specific Asset. 

I recommend you to explore the following solutions and threads that might help to achieve your goal:

Calculating Values For The Most Recent Date

Choose the latest date available in a dashboard filter

Best Regards, Lily

[DRay]

Hello @nnguyen95 

Did the information provided by @Liliia_DevX help you? If so, please mark it as the solution so that other users with the same question can find the answer. If not, please let us know so we can continue to help.

Thank you.

[nnguyen95]

Hello,

Unfortunately this solution was not exactly what we were looking for. We ended up using a workaround and calculating the metrics before and after the change made on the Config Start Date by pre-calculating these in SQL using the LAG function.

However, we'd like to see if there would be a way to solve this without pre-calculating in SQL. Is there any functionality in the Sisense formula builder that could potentially emulate the LAG function in SQL? This is what we were hoping to achieve using the PREV function, but as noted in Lily's response this is not how the PREV function works in actuality.

Please let me know if any further information will be needed.

Thank you!