-1

I have a columns DATE, TIME (only hour number) and GROSSVALUE

DATE          TIME     GROSSVALUE
2018-09-12    13       734564
2018-09-12    12       234324

How to use Scorecard to display GROSSVALUE for the current and previous hour?

IF currently is 2018-09-12 (and any time between 13:00 to 13:59)

EXAMPLE:

--------------

CURRENT

________________
|              |
|   734564     |
|______________|

PREVIOUS

________________
|              |
|   234324     |
|______________|
  • Could you elaborate by providing a publicly editable Google Data Studio Report (additionally, a Google Sheet if it's the data set) of the scenario (using sample data that shows 1) Input values (~10 rows) 2) Expected output 3) An attempt at solving the issue)? It would help users visualise the issue and test out suggestions on a specific use case with objective right / wrong answers. Without a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) it would be difficult to pinpoint a suggestion and the issue, e.g. Data Set, Data Source, Report, Fields, Chart – Nimantha Feb 16 '22 at 03:01

1 Answers1

0

Does the BigQuery LAG() function get what you're after? Something like:

SELECT GROSSVALUE
   , LAG()GROSSVALUE AS GROSSVALUE_last_hour
FROM DATASOURCE
ORDER BY DATE
   , TIME
rocksteady
  • 1,697
  • 14
  • 18