4 Replies Latest reply on Oct 17, 2017 9:41 AM by Shinichiro Murakami

# IF Date = xxxx THEN Sum(QTY)

Our sales years are not calendar years so I write a simple calculated field for example:

[Sales Year]

DATE(IF [OSDate] >= Date("2015-08-01") AND [OSDate] <= Date("2016-07-31") THEN '2016'

ELSEIF [OSDate] >= Date("2016-08-01") AND [OSDate] <= DATE("2017-07-31") THEN '2017'

ELSEIF [OSDate] >= DATE("2017-08-01") AND [OSDate] <= DATE("2018-07-31")THEN '2018'

END)

I am easily able to now show the sum of sales for each of those years.  What I want to do is compare the years together and show % of change.  I feel like I've done this in the past, but it's escaping me now.

Something like (If [Sales Year] = 2016 THEN sum(QTY).  Obviously this won't work, but can someone help me out with my logic.

I am trying to show on a filled map, change YoY (+/-).

• ###### 1. Re: IF Date = xxxx THEN Sum(QTY)

Hi Jason,

In this case,

You can simply change the start month of fiscal year?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: IF Date = xxxx THEN Sum(QTY)

Shin,

That is simple and extremely helpful, thank you!  I can't believe I haven't stumbled across that feature yet.

What about attempting to compare my sales ex: (2015 vs 2016) I want to show shading on a map for % of change.  I was even thinking I'd like to create buttons so the dashboard will automatically complete the calculations depending on what two sales years the user selects.  I'm not sure this is even possible in Tableau.

• ###### 3. Re: IF Date = xxxx THEN Sum(QTY)

Everything is possible in Tableau!

Right click on your measure, quick table calculation > percent difference > compute using > [Year]

This will give you year over year growth. Alternatively, you could use parameters to allow your users to select 2 comparison years.

For example, parameter 1 could be:

CASE [Parameter 1]

When '2016' and YEAR([Date]) = 2016 then [Measure]

When '2017' and YEAR([Date]) = 2017 then [Measure]

END

Essentially the same for Parameter 2. Your YoY growth calculation is then [Calculation from Parameter 1]/[Calculation from Parameter 2] which would give you % increase/decrease.

Does that help?

• ###### 4. Re: IF Date = xxxx THEN Sum(QTY)

Hi Jason,

We need DATA always.