Shift Time Frame In a Dashboard Using a Input Variable

[victor_correa]

I'm building a comparative transaction dashboard that should display day for the current time frame but also data shifted by a number of days that will be a variable input.

The goal is to be able to show current data versus a comparison with what the numbers look like "N" days ago and then calculate the variance.

The structure of the table I am trying to create would look like this:

I have been able to create something close to what I need using the following:

fetch bizevents,from: now()@d - (toLong($TxCountNDaysAgo) * 1d), to:now()@d - ((toLong($TxCountNDaysAgo)-1) * 1d)

But if I select a time frame with a specific start time and end time, the shifted data will show the data for the full day, rather than just shifted by the number of days I have chosen.

I have tried shifting time using Unix Time, but then I can't set the parameter "from" and "to" with the values I generated:

fetch bizevents 
| fieldsAdd currentTimeStampUnix = unixSecondsFromTimestamp(timestamp) 
| fieldsAdd shiftedTimeStampUnix = unixSecondsFromTimestamp(timestamp) - 86400 
| fieldsAdd currentTimeStamp = timestampFromUnixSeconds(currentTimeStampUnix), shiftedTimeStamp = timestampFromUnixSeconds(shiftedTimeStampUnix)

I'm wondering if anyone has done something along those lines and can share their insights on how they accomplished it.

Thanks in advance.

[victor_correa]

Update:
I have managed to do the time shifts statically by using Unix Time and the Internal Variables "dt_timeframe_to" and "dt_timeframe_from":

fetch bizevents,
   from: timestampFromUnixSeconds(unixSecondsFromTimestamp($dt_timeframe_from) - 86400), 
   to: timestampFromUnixSeconds(unixSecondsFromTimestamp($dt_timeframe_to) - 86400)

But Dynatrace won't let me use variables in the "from" and "to" parameters, so I have to define the shift explicitly with numbers, which defeats the purpose of what I'm trying to do.

Dynatrace won't let me change the values of the interval variables either, as that would solve my problem.

[zietho]

can you explain the following further please? 

"But Dynatrace won't let me use variables in the "from" and "to" parameters,"

[krzysztof_hoja]

You can use whatever is needed in from: and to: as long as it is constant expression. Here is the example of a query showing side by side data shifted by selected number of days (defined in variable)

fetch bizevents,
   from: toTimestamp($dt_timeframe_from), 
   to: toTimestamp($dt_timeframe_to)
| makeTimeseries x = count()

| lookup [

fetch bizevents,
   from: toTimestamp($dt_timeframe_from) - $offset:noquote * 1d, 
   to: toTimestamp($dt_timeframe_to) - $offset:noquote * 1d
| makeTimeseries x_ref = count()

], sourceField: interval, lookupField: interval, fields: { x_ref }

Result looks like this, but you are free to perform any other operation on data.