5 Replies Latest reply on Feb 23, 2016 5:41 AM by Mark Fraser

Difference Between 2 seasons Sales

Hi guys,

I have a bar chart that shows me sales of holiday and sales of fall season.

I need to add in another bar chart showing me the difference between these 2.

I was thinking of creating a calculating field that allows me extract exactly the difference but now sure how. (What is the forumale for subtracting the exact season from a different sales using the sales measure) Sorry Im quite noob on this.

My dimensions are "Seasons" its a string. (Seasons comprise of Fall, Holiday, Spring, Summer)

And Sales as a measure.

• 1. Re: Difference Between 2 seasons Sales

Hi Sunil

The easiest way would be to create buckets of sales for each season, then you can compare as you wish

What you didn't mention is whether you have multiple years/ and multiple seasons, I'll assume not... for now

IF [Season] = 'Fall' THEN [Sales] ELSE NULL END

IF [Season] = 'Summer' THEN [Sales] ELSE NULL END

etc.

That will give you 4 new measures, with only the sales for that season.

Then for comparison you would simply reference each as you need

SUM([Fall])-SUM([Summer])

PS.

If you had Year, you could extend the formula > IF [Year] = '2015' AND [Season] = 'Fall' THEN [Sales] ELSE NULL END

Hope that's clear!

Cheers

Mark

• 2. Re: Difference Between 2 seasons Sales

Hey Mark,

That is brilliant! I will have a try when i get back to office tomorrow.

One question though. Lets say i want to pull out the sales number by more than a dimension. So lets say i want to pull out Holiday and also Football (that falls under a dimension called category).

Do i simply comma? Even if it is more than 2?

If Season= 'Fall' AND IF Category= Football then sales else null end. Sry using phone so didnt have the brackets and all.

How do we pull a calculated field on a variety of dimensions.more than 3 lets say

PS sorry! Missed your year example. But lets say i have 3 or more dimension, i just keep using AND?

• 3. Re: Difference Between 2 seasons Sales

If Season= 'Fall' AND IF Category= Football then sales else null end. S

Hi Sunil

In this example -

If Season= 'Fall' AND IF Category= Football then sales else null end

You wouldn't repeat the IF, apart from that, its fine, except the brackets, but you know that!

If [Season]= 'Fall' AND [Category] = 'Football' then [Sales] else null end

Just to be complete, you don't need the 'else null' part you could write just

If [Season]= 'Fall' AND [Category] = 'Football' then [Sales] end

Which would also work, but personally i like to put the fail, just for completeness... you never know!

PS sorry! Missed your year example. But lets say i have 3 or more dimension, i just keep using AND?

Basically, yes.

Just be aware, that all conditions will need to be satisfied, in order to return.

And that more tests, take more time to perform, so be aware if you have massive data sets.

You could also use OR in place of and, or in combination.

What i am describing are known as logic/logical tests

I would strongly advising reading up some more and get confident with them, they are SO important in programming.

They pop up elsewhere in Tableau > ISNULL, ISDATE

Finally, there are 2 types - IF and CASE

They work slightly differently, and have performance differences.

You can read more here > https://www.interworks.com/blog/tmcconnell/2015/02/10/case-statements-vs-if-statements-tableau

Cheers

Mark

• 4. Re: Difference Between 2 seasons Sales

Thanks alot Mark! Really appreciates it!

• 5. Re: Difference Between 2 seasons Sales

Sunil, you're very welcome!

I could go on... but I think that's enough to give you a very good start - of course if you have any questions do use the forums...

Master logical statements, and you'll have a very good career