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?
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.
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.
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.
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.
1 of 1 people found this helpful
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!
Thanks Jon for offering your help here, as I obviously was not clear on the goal!
No worries! Happy to help.
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.
Here are a few points in time and the percentages they should be at.
On 10/21: 0.78%
On 10/31: 1.62%
On 10/21: 4.76%
On 10/31: 3.64%
We just did this couple weeks back at our work and thought to chime in..
-- Sum of clicks to date
-- Sum of impressions to date
-- Clicks over impression
In the image below showing clicks/impressions = value as the label.
Hope this helps..
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.
Thank you very much. This accomplishes what I need to do.
I appreciate the help and time from all!