Delta Column in Looker Table Chart

[Shafeeq_Rahaman]

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

[Dawid]

HI @Shafeeq_Rahaman,

You need to build each delta as a Table Calculation using offset function. Have a look at this article:

Looker Help Center

Calculating Percent of Previous and Percent Change with Table Calculations

Table calculations, as discussed in Using Table Calculations, make it easy to create on-the-fly metrics and enable the creation of calculations that operate on the table data returned by a SQL quer...

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

[Shafeeq_Rahaman]

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 .

[Dawid]

Offset is relative to each row, have a go and let us know what the results are and whether more tweaks are needed

[PatKearns]

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...

[Shafeeq_Rahaman]

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

[PatKearns]

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

[Shafeeq_Rahaman]

PatKearns:

ather hacky, you can turn the color for yesterday’s data in the edit viz to white, so it disappears in the background.

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.