3 Replies Latest reply on Jan 10, 2014 3:23 PM by Joe Mako

# How to perform an Earlier function in Tableau

### Richard Leeke Joe Mako

I am trying to perform a calculation in tableau previously performed in powerpivot.

Essentially the powerpivot formula uses the Earlier function. Do you have an idea for how to do this, below I have provided an elaboration of the earlier function.

### Flashback:   Simple Use of the EARLIER Function

This is an excerpt from a post last month, “reprinted” here for convenience.

Say I have the following VERY simple table like this:

And I want to add a third column that is the total for each customer:

The calc column formula for that third column is this:

=CALCULATE(SUM([Amt]),
FILTER(ALL(‘Table’),
‘Table’[Customer]=EARLIER(‘Table’[Customer])

)
)

Note the highlighted part:  in that formula we are filtering on ALL(Table) rather than just the “raw” Table.  Here’s the crux:

When I say FILTER(ALL(Table)), all of my references to columns in Table will have “forgotten” all notion of “current row” and will instead be references to the entire column.  That is because of the ALL().

So the EARLIER() function is my escape hatch that allows me to go back and inspect the current row’s value.

This line of the formula:

‘Table’[Customer]=EARLIER(‘Table’[Customer])

Can be understood as:

‘Table’[Customer]=CurrentRow(‘Table’[Customer])

• ###### 1. Re: How to perform an Earlier function in Tableau

How about a calculated field with a formula of:

TOTAL(SUM([Amt]))

and then ensure your Partitioning is the Customer Dimension.

If you can provide a sample packaged workbook that represents your situation more details can be provided, as there may be other subtle factors that are having an impact and need to be taken into consideration.

• ###### 2. Re: Re: How to perform an Earlier function in Tableau

Thanks Joe Mako

Attached is the worksheet.

Formula for CodeNextValue is:

CALCULATE(sum(Attempts[CodeNumber]), FILTER(Attempts, (Attempts[Customer_ID]=EARLIER(Attempts[Customer_ID])) && (Attempts[AttemptNo]=(EARLIER(Attempts[AttemptNo])+1))))

Note that above "Attempts" is the table name.

In the attached worksheet customer ID 1017504742 has 4 different attempts. I am trying to duplicate creating the CodeNextValue in tableau as is done in Powerpivot.

• ###### 3. Re: Re: Re: How to perform an Earlier function in Tableau

Alex, unfortunately I do not know PowerPivot, nor am I able to derive your logic from looking at static numbers.

For example, why is cell F2's value '100'? where did that value come from?

If that cell should really be blank, how about the attached that uses the formula:

LOOKUP(ATTR([Code Number]),1)

1 of 1 people found this helpful