Compare measures: current value vs last N minutes

[harpar1808]

Hello,

I have timeseries snapshots like:

SnapId, Time, Symbol, PnL

1, 10/1/2025 08:00:00, MSFT, -20

1, 10/1/2025 08:00:00, IBM, 50

2, 10/1/2025 08:15:00, IBM, 25

2, 10/1/2025 08:15:00, MSFT, 200

3, 10/1/2025 08:30:00, IBM, 100

3, 10/1/2025 08:30:00, MSFT, -100

I want to calculate chart with delta PnL differences between different snapshots at symbol level between 2 snapshots which are 30 mins apart. So, here the delta PnL between snapshot 3 and 1 (since they are 30 mins apart) would be: IBM: 75, MSFT: 50, AAPL: -80 . So now, you can imagine I can have a table with Symbol, Delta PnL

Symbol, Delta PnL

IBM, 75

MSFT, 50

AAPL, -80

The time comparison has to be between time when user opens the dashboard and minus 30 mins.

How can I achieve this?

Thank you.

[joshdemu]

hi - please clarify which PnL values you are trying to compare. are you looking to compare the two values only from snapshot 1 and 3 and ignore 2? i’m asking because the math in the example is not adding up for me. where is 75 coming from for IBM for example.

also, will the data always be in :15 minute intervals?

you should be able to do this using calculated fields but understanding more about the use case would be helpful.

[harpar1808]

Yes, apologies. the comparison should be between 1 and 3. The use case is whenever the dashboard is opened, it calculates the PnL difference between that time (now() - 30 mins).

I am putting this as the calculated field:

periodOverPeriodDifference(sum({PnL}), now(), MINUTE, 30) but this is not working:

Can you please asdvise?

Thanks.

[harpar1808]

Hello @joshdemu,

Any update here please?

Thanks.

[joshdemu]

you could create a calculated field that returns a boolean if a row matches the criteria you want. for example, if are checking exactly 30, you could do this:

dateCheck: ifelse(dateDiff(now(), parseDate({Time}, "MM-dd-yyyy HH:mm:ss"), "MI") = 30, 1, 0)

then once you have this you could get the latest PnL from the data then compare to the PnL where this new field has a 1. would that work for your use case?

[harpar1808]

Ok, I did something similar. Created following calculated fields:

1. to get latest snapId –> LatestSnapId: maxOver({SnapId}, [{SnapId}], PRE_AGG)

2. to get 30 min old snapId in my dataset –> 30MinSnapId: LatestSnapId-6

3. the PnL difference –> PnlDelta30Mins: ifelse({archive_run_id}=LatestSnapshotId, {hcur_net_daily_pl}, 0) - ifelse({archive_run_id}={30MinOldSnapshotId}, {hcur_net_daily_pl}, 0)

I have a filter for today but when I run this, it calculates for all my snaps (which are 5 min apart):

what am I doing incorrect here?

[joshdemu]

are you open to filtering to a single symbol or maybe a visual per symbol to make it work? there is a solution that uses a lag function in a calculated field. however, unless you do some math on the offset based on how many symbols you have + your time offset, it won’t work as you expect.

for example, i can have these three calculated fields based on your example:

30MinOldSnapshotId: lag(max({snapshot_id}), [{as_of_date} ASC], 6)
LatestSnapshotId: max({snapshot_id})
PnLDelta30Min: sum({current_pnl}) - lag(sum({current_pnl}), [{as_of_date} ASC], 6)

notice the period 6 is because we have 5 minute data and we want 30 minutes.

here’s an example of what it looks like, filtered for only 1 symbol (i have another calculated field to get the 30 min ago value just to test for accuracy).

but without a single symbol the offset is not correct:

[WLS-Luis]

Hi @harpar1808,

Hope everything is well! Were you able to see joshdemu’s most recent message and/or find a workaround yourself in the meantime? If we do not hear back within the next 3 business days, I’ll go ahead and close out of this topic.

Thanks!

[WLS-Luis]

Hi @harpar1808,

Since we have not heard back, I’ll go ahead and close this topic. However, if you have any additional questions, please feel free to post again in the Quick Community and link this thread for any relevant information!

Thanks!