Hi,
I am looking to add a delta column in my table to each of the measure, i.e., comparison of yesterday vs day before (day over day percent change)
Could someone let me know how can I achieve this?
Ex: Impression (column name) should show yesterday value and a new column beside it to have the percentage difference of yesterday vs day before
Would like to have a delta column for each measure i.e., impressions, clicks
HI @Shafeeq_Rahaman,
You need to build each delta as a Table Calculation using offset
function. Have a look at this article:
Basically what it would look like for you:
${impressions} / offset(${impressions}, -1)
or
(${impressions} - offset(${impressions}, -1)) / offset(${impressions}, -1)
If you wanted to know the percentage of the delta
Hi @Dawid
Thank you for the reply.
But won’t offset just compare second row against the row above ?
Currently in screenshot the impressions column is showing value from today, I want the second column ‘delta’ to compare this value with yesterday’s value and show percent change .
Offset is relative to each row, have a go and let us know what the results are and whether more tweaks are needed
try pivoting on your date (and the day before) and using pivot_offset()
. https://help.looker.com/hc/en-us/articles/360023624254-Using-offset-and-pivot-offset-in-Table-Calcul...
Hi @PatKearns
This helps I can create a delta column by filtering for last 2 days, pivoting them and later using pivot_offset.
But is there a way to hide previous day data, for example 07/27/2020 data does have anything to provide, but 07/28/2020 data in the screenshot has the value of leads, delta (change to previous) etc
Ah true, well, rather hacky, you can turn the color for yesterday’s data in the edit viz to white, so it disappears in the background.
otherwise, you might be better off making 3 measures in lookML 1) todays value 2) yesterdays value 3) the delta
Well the whitening is a probable way but will cause a space gap, I guess LookML is the only way, was avoiding as I dont have access to it.