6 Replies Latest reply on Apr 8, 2013 7:00 AM by Jonathan Drummey

# Create a Variable using previous information of itself.

Dear all,

I need to create a variable that uses previous information of itself and information from other variable at the same time. This is a small example using Excel in which I create variable Y using the following logic:

The first observation of variable Y (let’s call it Y1) is equal to the first observation of variable Z (Z1) which is constant over the time. The second observation of Y has the following form: Y2 = Y1*a + X2, where “a” is parameter and X2 is the second observation of variable X. Subsequently, the third observation of Y will be Y3 = Y2*a+X3… and so on.

The question is simple, how I can construct variable Y in Tableau? I have tried several ways with no success. The following table is an example of the desired Y variable and the file has the very same information if you require more clarity. Thank you so much for your help.

Andres.

• ###### 1. Re: Create a Variable using previous information of itself.

Andres,

Here's a calculation:

IF FIRST() = 0 THEN LOOKUP(SUM(z), 0)

ELSE PREVIOUS_VALUE(SUM(z)) * a + ZN(LOOKUP(SUM(x), 0))

END

To break it down:

If it is the first record, then the value for z is used

Otherwise it uses the previous value of the calculation * the value of parameter a + the value of x.

I've attached a workbook so you can see it.

Regards,

Joshua

• ###### 2. Re: Create a Variable using previous information of itself.

Hi Joshua,

I'm curious, why are you using LOOKUP(SUM(z),0)? In my experience, SUM(z) always returns the same results as LOOKUP(SUM(z),0) and would theoretically be faster because it's one less computation.

Jonathan

• ###### 3. Re: Create a Variable using previous information of itself.

Jonathan,

I don't have a good reason.

It does indeed return the same result, is probably more efficient, and definitely more readable like this:

IF FIRST() = 0 THEN SUM(z)

ELSE PREVIOUS_VALUE(SUM(z)) * [a] + SUM(x)

END

1 of 1 people found this helpful
• ###### 4. Re: Create a Variable using previous information of itself.

Ok, glad I wasn't missing anything!

• ###### 5. Re: Create a Variable using previous information of itself.

Dear Joshua, Thank you so much !!!!

It was super useful !!!. I already applied it in my real problem and it worked very well. However, I still do not understand very well the PREVIOUS_VALUES() function because it gives me the same result with different values for  the expression inside the function. For instance, if I put a -1, or a 1, inside it gives me the same results. Like this:

IF FIRST() = 0 THEN SUM(z)

ELSE PREVIOUS_VALUE(-1) *[a]+ sum([x])

END

Do you know why?

Thank you again.

• ###### 6. Re: Create a Variable using previous information of itself.

PREVIOUS_VALUE() is an iterative function, in other words it acts on the calculation it's used in. The argument given to PREVIOUS_VALUE() determines its data type and is used for the first row of the partition if there is no value set. Then for succeeding rows in the parition, PREVIOUS_VALUE() returns the value of the prior row of the partition of itself.

For example, PREVIOUS_VALUE(MIN(0))+1 has the same results as INDEX(). It starts with a value of 0 and adds 1 to it for the first row in the partition, then for the second row has a value of 1+1 = 2, then for the third row has 2+3 = 3, etc. Note that if you use PREVIOUS_VALUE(0)+1, that won't work because PREVIOUS_VALUE() is a table calc and expects an aggregate (except for a special case that you might have noticed already).

In the case of the calculation that Joshua put together, the IF FIRST()==0 THEN SUM(z) sets the value for the first row of the partition, so it mostly doesn't matter what the argument given to PREVIOUS_VALUE() is because it will never be used - the only bit that matters is that the argument has the correct data type. In the case of your particular formula because of that IF FIRST()==0 then the instance of PREVIOUS_VALUE(-1) can even get away with not having an aggregate as an argument.

Jonathan