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

    Difference Between 2 seasons Sales

    Sunil Rai

      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
          Mark Fraser

          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
            Sunil Rai

            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
              Mark Fraser

              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
                Sunil Rai

                Thanks alot Mark! Really appreciates it!

                • 5. Re: Difference Between 2 seasons Sales
                  Mark Fraser

                  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