Create a Single Value chart that compares to the previous period

[Alexander_Arozi]

Hi

I want to create a Single Value chart which shows our conversion rate. I Also want it to display the change from the previous period.

So if I show the conversion rate for the last 7D (say 2-8 june) I want the comparison value to be the conversion rate for the 7D before my viewed period (in this case 26 may-1 june)

I guess that I have to create a custom field to shift the data of the same conversion value. so it gets shifted N days back (7 days in this case). I also want it dynamic , so If I’m watching the last 14 days, the comparison value should be the 14 days before viewed date range, and so on.

How do I go about doing this?

regards
Alex

[blue1]

Hi Alexander,

I also had this problem.

The way I solved it was using three calculated fields: Moving Average (Last Week), Moving Average (Prior Week), % Change.

I changed my values and labels to better approximate your use case.

In your post, you talk about “watching” days vs. “before viewed” days. Unless you want to create a persistent derived table (PDT), this is the only way to get it, as far as I know. That means, if you are interested in “watching” 14 days, then you have to set your query to 28 days so that the calculated field can calculate the “before viewed” days.

I will go through the code below. In some parts, you have to insert the path to your measure.

Moving Average (Last week)

Formatting: Percent(2)

sum( if( row() <= 14, ${YOUR_MEASURE}, 0 ) )
/ count( if( row() <= 14, ${YOUR_MEASURE}, null ) )

Moving Average (Prior week)

Formatting: Percent(2)

sum( if( row() <= 28 , ${YOUR_MEASURE}, 0 ) )
/ count( if( row() <= 28 , ${YOUR_MEASURE}, null ) )

Moving Average (Prior week)

Formatting: Percent(0)

(${moving_average_last_week}-${moving_average_prior_week})/${moving_average_prior_week}

All that is left to do is to set up the comparison.

Making it dynamic

In order to make this whole thing work dynamically, I would replace the hardcoded values (14, 28) with

round(max(row())/2,0)

We can change the logic for the calculated field then.

Moving Average (Dynamic 1st Half)

Formatting: Percent(2)

sum( if( row() < round(max(row())/2,0), ${YOUR_MEASURE}, 0 ) )
/ count( if( row() < round(max(row())/2,0), ${YOUR_MEASURE}, null ) )

Moving Average (Dynamic 2nd Half)

Formatting: Percent(2)

sum( if( row() >= round(max(row())/2,0) , ${YOUR_MEASURE}, 0 ) )
/ count( if( row() >= round(max(row())/2,0) , ${YOUR_MEASURE}, null ) )

In that way, you will always compare two equal halves no matter how big the timeframe.

I hope this helps!
Let me know if you have any questions about the code!

[Alexander_Arozi]

Hi Jasper, and thanks for your answer.

I got it to work after some tinkering, but it now works perfectly.

The only issue is that we have to choose a span of 28 days to see the last 14 days, which can be a bit confusing since this will be a public dashboard within the company. we saved it for now by just changing the “change” parameter to “first half of period vs. last half of period”.

anyways, thanks for the help and all!
/A