Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Calculations are correct (technically) but they sure look wrong...
I want to show percentage increase/decrease in learning approaches, comparing September, 2007 with September, 2008.
This seems easy enough, as we can just use the percentage difference calculation. But have a look at the first image...(trend1.jpg)
See trend1
If you do some quick calculations by hand, you'll see that the computed percentage differences are slighly off.
It turns out the problem is because the number of survey responses in 2007 is different than 2008 (3,108 responses in September 2007 vs. 3,005 for the same month in 2008).
See Trend2
Because the number of responses are almost the same, the calculation isn't too far off. However...
Here's another example:
See Trend3
Hmm. Adobe went from 26.3% market share to 24.9%. That's a 1.4/26.3=5.32% decrease.
But look at the calculated results:
See Trend4
So what gives?
It turns out Tableau isn't really looking at the percentage increase/decrease based on the marketshare percentage; it's looking at the number of people using the tool in 2007 vs the number in 2008.
But the number of responses is not the same for both time periods:
See Trend5
So, how do I "normalize" this; that is, either come up with a mechanism that has the same number of records for both time periods or that does the percentage difference based on market share percentage, and not headcount?
Your assistance is greatly appreciated; I sure don't want to drop in Excel.
| Attachment | Size |
|---|---|
| Trend1.jpg | 58.48 KB |
| trend2.jpg | 27.45 KB |
| Trend3.jpg | 42.04 KB |
| Trend4.jpg | 9.86 KB |
| Trend5.jpg | 15.76 KB |
Subscribe to the comments on "Calculations are correct (technically) but they sure look wrong..."
Comments
Have you seen the "Calculate the percent difference within" selection when editing your table calc? In addition to Down/Across there should be a column name or two, one of which should be the "Survey score" or "Market share" column.
I feel like that's what you're looking for, but I'm not quite sure from your cropped screenshots.
James,
Thanks for getting back to me.
I have tried that, but still run into the same problems. Perhaps I should give some details as to what data we have an how we are using calculated fields.
We take snapshots of the data every month. The system is designed to compare this months data with that from one year ago (next month, it updates automatically).
To be in "this year's" data, we look at survey responses that were updated in the last 365 days (we don't want to take older than that). As we don't yet have data that's more than two year's old, I don't have to apply the same "freshness" restriction on stuff that is older than one year (but I will have to do this soon).
So...
On the columns shelf I have the weighted modality rating (some number between 0 and 5). On the rows shelf I have the Modality followed by a MONTHYEAR (MY) of the following calculated field:
IF YEAR([vd_modalities_hist_archivedate]) =year(today())-1 AND month([vd_modalities_hist_archivedate])=month(today()) then today()-(365+day(today()) )
elseIF YEAR([vd_modalities_hist_archivedate]) =year(today()) AND month([vd_modalities_hist_archivedate])=month(today()) AND DATEDIFF('day',[lastmodalityupdate],TODAY( ) )<(365+day(today())) then today() end
Not sure what to do, or what to share so you can better diagnose, but the number of responses seems to be skewing the calculation.
Steve
Steve,
What is the formula for the 'market share percentage' calculation you want to display?
And also, is that the field you are doing the %difference on?
thanks,
:)ross
For the marketshare visualization, I'm doing a countd(organization) so I can calculate the number of unique organizations that use a particular tool.
Calculation Type: Percent Difference From
Calculate the percent difference within: ThisMonth_OneYearAgo
Display the current value as a percentage of: Previous
At the level: Deepest
Steve
Gotcha. Here is why you aren't seeing what you expect:
Market share % = % of total countd(organization) within ThisMonth_OneYearAgo =>
countd(organization)/TOTAL(countd(organization))
where TOTAL is the total across all tools for that year.
Percent difference = countd(organization)/PREVIOUS(countd(organization)) - 1
where PREVIOUS is the count from the previous year.
what you WANT to do is
percent difference = Market share % - PREVIOUS(Market share %)
You need to compute a table calculation on a table calculation. The problem is that you can't combine table calculations that way in Tableau...yet.
:)ross
Yup. You put it more clearly than I did.
Do let me know when there's a way to do this.
And thanks for the attentiveness.
Steve