2 Replies Latest reply on Jul 30, 2012 2:25 AM by Siobhan Magee

# Calculated Field Question

Hi all,

I am new to Tableau so forgive me if this is an easy question!  I have a large dataset but the following is simplified to illustrate my question.  I have 3 columns of data:

Number Payments: 0, 1, 2, 3

Number Active: 100, 90, 80, 70

Number Lapsed: 10, 20, 30, 40

All I am trying to do is to calculate the % Lapsed of the total by payment.  The total is calculated by adding Number Active + Number Lapsed at Payment 0 so in this case it should be 110.  The %Lapsed therefore would be 18% at payment 1, 27% at payment 2, 36% at payment 3.  In Excel I would just absolute the sum of active and lapsed at payment 0 to calculate the %.

It shouldn't be that difficult so I am missing something obvious.

Any suggestions would be appreciated!

Many thanks.

• ###### 1. Re: Calculated Field Question

Hi,

How about something like the attached? Given the columns you outlined, the % Lapsed calculation is set up as:

SUM([Number Lapsed])/(SUM([Number Active])+SUM([Number Lapsed]))

The Number Payments field is set up as a discrete dimension on the Rows Shelf in order to generate a row for each value of Number Payments.

Cheers,

Jonathan

• ###### 2. Re: Calculated Field Question

Thanks for your suggestion Jonathan.  It didn't work in this case as I need the total at 0 payments to be a constant.  I sought external help and solved the problem by using the Window_max function:

sum(number_active) / window_max (sum(total gifts)) where total gifts is [Number Active]+[Number Cancelled].

Cheers,

Siobhan