4 Replies Latest reply on Sep 13, 2013 4:25 AM by Steven Blanc

# Sales vs Sales Target based on Previous Year Sales

Good morning,

I am looking for a little help here.

I have two calculated fields based on Sales the first is [Sales (2013)] and the second is [Target (2013)]. These two fields are calculated as follows:

Sales (2013)

IF

YEAR([Document Date])=2013

THEN

[Sales]

END

Note: I know that I can simply use Document Date to break up my sales by month and year, but this has caused me layout problems. This problem remains regardless of the method of displaying my sales figures.

Target (2013)

IF

YEAR([Document Date])=2012

THEN

(1+[Target Percentage])*[Sales]

END

I would like to work out the difference between these two figures using [Target (2013)] - [Sales (2013)].

I am assuming that my issue is that Tableau cannot compute a single calculation with multiple document dates. I effectively get all null figures.I tried creating a [Target Date] calculated field which was equal to [Document Date] + 365 but realised that has the same fundamental flaw. Any idea how might I get around this issue?

Cheers,

Steven

• ###### 1. Re: Sales vs Sales Target based on Previous Year Sales

Its very difficult to describe how to solve a problem in Tableau at times--if you can post a packaged workbook that demonstrates the issue, forum members will be glad to help you find an approach that works for your case.

• ###### 2. Re: Re: Sales vs Sales Target based on Previous Year Sales

Fair enough. Here is packaged workbook using some one of the sample data sets.

The only difference between the sheets 'targets' and 'targets (alt)' is the use of a target date which is equal to ship date + 1 year.

I need to calculate the difference between the target which is a function of ship date at time 0 and sales which is a function of ship date at time 1.

• ###### 3. Re: Sales vs Sales Target based on Previous Year Sales

In your "sales" sheet, you could use a Table Calculation like this: LOOKUP(ZN(SUM([Target])), -1) - ZN(SUM([Sales]))

In other words,"target computed at year (n-1) minus real sales from year n" (table across)

1 of 1 people found this helpful
• ###### 4. Re: Sales vs Sales Target based on Previous Year Sales

That's an interesting concept, I hadn't thought of that. However, that approach broadens the the number of columns significantly. How would I achieve the same result while keeping 5 years of sales and only the last year of targets?