1 2 Previous Next 15 Replies Latest reply on Nov 4, 2014 10:26 AM by KK Molugu

# How To Visualize Cumulative Results Over Time?

Hello,

I am trying to visualize cumulative click through rates over time. Click through rates are notoriously volatile depending on the amount of impressions served, so I would like to account for fluctuations using a cumulative trend line.

I've tried this using Window_SUM, but it doesn't trend over the date ranges, rather just provides the total figures.

Attached is a packaged workbook with the chart showing weekly click through rates that I'd like to convert into a trended cumulative viz.

• ###### 1. Re: How To Visualize Cumulative Results Over Time?

I'm not clear on what you're hoping to achieve.  Can you provide a mockup of your expected results, or otherwise add detail on what you hope to achieve here?

• ###### 2. Re: How To Visualize Cumulative Results Over Time?

I am trying to achieve something like this, which was done in Excel.

The calculation for cumulative click through rate is = SUM(clicks)/SUM(impressions).

So for example with the cumulative trend, on 9/1 it would be showing the sum of clicks from 7/1 through 9/1 divided by impressions from 7/1 through 9/1.

• ###### 3. Re: How To Visualize Cumulative Results Over Time?

I'm not really clear on what I'm looking at -- the workbook you posted has a Click Through Rate % across Weeks, colored by three different Sites, so there are three lines.  The image you've uploaded has far more lines, so I'm afraid I am still not really clear on what you are hoping to achieve.

Your calc in Tableau matches what you've stated here -- if you remove Site, you'll then be looking at the SUM(Clicks) / SUM(Impressions) across whatever Date Range you filter on.

I'm just not clear on what you're after, and how it differs from what you've already created in the TWBX you posted.

• ###### 4. Re: How To Visualize Cumulative Results Over Time?

I appreciate you trying to help.  The lines are indeed just the site dimension on color, but the calculation is not dependent on the number of sites. The screen shot is from another dashboard with more sites included.

At the very basic level, I'm trying to have a chart that can work off a calculated field (clicks/impressions) and show cumulative results on each day.

So if something starts out and is at a 0.20% cumulative click through rate on 9/1, but by 10/1 it's at a 0.40% cumulative click through rate, I would want to see that trend reflected in the chart.

• ###### 5. Re: How To Visualize Cumulative Results Over Time?

Its just difficult to help without understanding the results you're after.  A mock up of your actual expected results for this data set is about all I can suggest, in terms of clarifying the end goal (at least for me).

Someone else may very well jump in and understand immediately, but without understanding the results you're looking for, I am afraid I don't know how to help.

• ###### 6. Re: How To Visualize Cumulative Results Over Time?

If I understand correctly, you're looking for a moving average of CTR over time.  To do that, just add a quick table calc to your Click Through Rate % calculation in the Rows shelf.

Now, you can fine-tune the moving average by editing the table calc.  In this case, I chose the previous 3 months (including the current value):

Hope this helps!

1 of 1 people found this helpful
• ###### 7. Re: How To Visualize Cumulative Results Over Time?

Thanks Jon for offering your help here, as I obviously was not clear on the goal!

• ###### 8. Re: How To Visualize Cumulative Results Over Time?

No worries!  Happy to help.

• ###### 9. Re: How To Visualize Cumulative Results Over Time?

Thanks for your help, Jon. I'm actually not looking to do a moving average, I'm looking for cumulative results at any given date in the chart.

This again is the working version I have in Excel. I annotated what I'm trying to achieve. This has more lines than my workbook, but the goal is the same.

• ###### 10. Re: How To Visualize Cumulative Results Over Time?

Do you have any idea what your expected results would be for the data you've uploaded?  It might help to upload the actual Excel file, so we can see the logic behind the chart you are referencing in your screenshots.

• ###### 11. Re: How To Visualize Cumulative Results Over Time?

Here are a few points in time and the percentages they should be at.

Site 1:

On 10/21: 0.78%

On 10/31: 1.62%

Site 2:

On 10/21: 4.76%

On 10/31: 3.64%

• ###### 12. Re: How To Visualize Cumulative Results Over Time?

Jonathan:

We just did this couple weeks back at our work and thought to chime in..

-- Sum of clicks to date

RUNNING_SUM(SUM([Clicks]))

-- Sum of impressions to date

RUNNING_SUM(SUM([Impressions]))

-- Clicks over impression

[Clicks-All]/[Imp-All]

In the image below showing clicks/impressions = value as the label.

Hope this helps..

..kk

• ###### 13. Re: How To Visualize Cumulative Results Over Time?

I'll let KK take the wheel -- the dates you listed are not included in the View you posted, as you have the view set up at the WEEK(Date) level.  So, I'm even more confused unfortunately.

I believe KK's solution is probably accurate (or close to what you're looking for), but notice he's analyzing at the Day(Date) level.  That is an important bit in what he has set up.

• ###### 14. Re: How To Visualize Cumulative Results Over Time?

Thank you very much. This accomplishes what I need to do.

I appreciate the help and time from all!

1 2 Previous Next