This is definitely possible.
If you have a sample dataset we can help you achieve this.
It's a bit troublesome to "generate" additional column in Tableau, then I propose different approach.
With using dashboard, you can show existing data and calculated prorated data next to each other.
And in Sheet 4, you can filter only the latest year.
First of all,
You'd better have data pivoted, if you don't have flat data.
Flat data means the data structure which only has one data column and "paid", "reserve", "volume" as field name.
Then create sheet 2.
I prepared parameters with the latest month, and add calculated field to calculate how many months has passed.
case [Parameter 1]
when "January" then 10
when "February" then 11
when "March" then 12
when "April" then 1
when "May" then 2
when "June" then 3
when "July" then 4
when "August" then 5
when "September" then 6
when "October" then 7
when "November" then 8
when "December" then 9
You can create "Month passed" parameter directly.
Then calculate prorated value.
if [Policy Year]= [The latest date] then [Pivot field values]/[Months passed]*12 end
And add one more field which is used as header and filter.
if [Policy Year]=[The latest date] then str ([The latest date])+" Prorate 12 month" end
2016 prorated_9.2.twbx 28.7 KB
Thanks for your help and comments guys.
I think I get what you mean Shin and thank you for the work you put in your explanation.
Could I be cheeky and show you the data as I have it.
The Total Paid and Reserve are calculated fields based on the addition of the AD & TP Values. The volume is a count of the Claims per Policy Year.
Pro Rata.twbx 25.8 KB
Using your workbook, I can create 4 more fields to calculate prorated value for 2016.
This is different approach from my initial post.
Then, same thing, to combine two sheets in dashboard with hiding the header with keeping same order.
And filter only 2016 for sheet 2.
[Number of Records (Pro)]
if [Policy Year]="2016" then [Number of Records]/[Months into Policy Year]*12 END
[Total Paid Pro]
if [Policy Year]="2016" then [Total Paid]/[Months into Policy Year]*12 end
[Total Reserve Pro]
if [Policy Year]="2016" then [Total Reserve]/[Months into Policy Year]*12 END
[Total Incurred Pro]
[Total Paid Pro]+[Total Reserve Pro]
Pro Rata_9.3.twbx 34.5 KB
Thank you Shin
I cant believe its so simple, I was thinking of all sorts of complex methods.
Thank you for your help