7 Replies Latest reply on Mar 31, 2014 2:54 PM by Vinh Ton

# how to calculate if current date aggregate is higher than previous

I'm trying to create a calculated field to conditional color format current month's color based on previous months:

• ###### 1. Re: how to calculate if current date aggregate is higher than previous

You'll want to look at the LOOKUP() and PREVIOUS_VALUE() functions.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: how to calculate if current date aggregate is higher than previous

Thanks, I tried this:

If SUM( [Actual] ) > PREVIOUS_VALUE( SUM([Actual]) )

THEN "G"

End

But Tableau complains that Previous_value is expecting float gets a string while

PREVIOUS_VALUE( SUM([Actual]) )

is ok

Actual are the values that generate the blue bars

• ###### 3. Re: Re: how to calculate if current date aggregate is higher than previous

You're looking for:

LOOKUP( SUM( [Sales] ),0 )>LOOKUP( SUM( [Sales] ),-1 )

You'll need to use Advanced and set the order in the addressing window correctly so you can restart on the right dimension. I set up a similar viz in using Superstore in the attached workbook.

--Shawn

• ###### 4. Re: Re: how to calculate if current date aggregate is higher than previous

Very nice, thanks!

• ###### 5. Re: Re: how to calculate if current date aggregate is higher than previous

Any way to change the legend without editing the calculated field? Similar to Aliasing?

• ###### 6. Re: Re: Re: how to calculate if current date aggregate is higher than previous

No. But there's usually a workaround. Here's what I came up with off the top of my head:

IFNULL( IF LOOKUP( SUM( [Sales] ),0 )>LOOKUP( SUM( [Sales] ),-1 ) THEN 'My True Label'

ELSEIF NOT LOOKUP( SUM( [Sales] ),0 )>LOOKUP( SUM( [Sales] ),-1 ) THEN 'My False Label'

END, 'My Null Label' )

(See attached.)

Cheers,

--Shawn

• ###### 7. Re: Re: Re: how to calculate if current date aggregate is higher than previous

Thanks, yeah thats my workaround as well