5 Replies Latest reply on Jul 26, 2016 1:51 AM by david Rowley

Calculation Against part of Dimension and applying to all Measures

Hi guys

In Excel i would have the below table and I can replicate it in Tableau up to column H

In Excel Column I would be a formula that would divide the measures in Column H by 3 then multiply by 12 to Pro rata the values e.g =H2/3*12

Is it possible to do this in Tableau using a parameter to set the months value, for all measures shown and based against 2016 only?

• 1. Re: Calculation Against part of Dimension and applying to all Measures

This is definitely possible.

If you have a sample dataset we can help you achieve this.

• 2. Re: Calculation Against part of Dimension and applying to all Measures

David,

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.

[Months 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

end

You can create "Month passed" parameter directly.

Then calculate prorated value.

[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

Thanks,

Shin

9.2 attached.

• 3. Re: Calculation Against part of Dimension and applying to all Measures

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.

• 4. Re: Calculation Against part of Dimension and applying to all Measures

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]

Thanks,

Shin

1 of 1 people found this helpful
• 5. Re: Calculation Against part of Dimension and applying to all Measures

Thank you Shin

I cant believe its so simple, I was thinking of all sorts of complex methods.