3 Replies Latest reply on Oct 14, 2016 10:30 AM by Ryan Slagle

    Calculate difference in Sales for selected date and Selected date-1

    tanya.kanwar.0

      Hi,

       

      I have to calculate the difference in sales between current date(this is selected based on the date parameter) and previous date.I created a calculated field called " difference" which subtracts the current date from the previous date. but doesn't populate anything.. Can any one help ??

      I have attached the workbook for reference .

       

       

      Thanks in advance !

        • 1. Re: Calculate difference in Sales for selected date and Selected date-1
          Ryan Slagle

          Hi Tanya,

           

          You were super close!

           

          Just needed to aggregate your difference calculation:

           

          Or put in Null handling:

          1 of 1 people found this helpful
          • 2. Re: Calculate difference in Sales for selected date and Selected date-1
            tanya.kanwar.0

            Can u please attach the workbook..?Also wondering why do we need to aggregate or check for null in order to get valid value..

            • 3. Re: Calculate difference in Sales for selected date and Selected date-1
              Ryan Slagle

              Of course, workbook attached.

               

              The primary reason why your [difference] calculation didn't work as expected was actually due to how you wrote your [Date's Sales] and [Date-1 Sales] calculations.

               

              Take [Date's Sales] for example:

              if [Order Date]=[Order Date Parameter] then [Sales] END

               

              This logic will catch any rows where the order date equals order date parameter, no problem there. But what about the rows that don't equal order date parameter? Since your IF THEN statement does not include an ELSE clause, all of the rows that do not equal order date parameter end up as NULL. You can see this when taking a look at the underlying data:

               

              Your original [difference] calculation had no aggregation: [Date's Sales] - [Date-1's Sales] This means that the calculation occurs at every single row, and since you never have a single row where both [Date's Sales] and [Date-1 Sales] have a value, Tableau is trying to subtract a value minus a null, or a null minus a value. Anytime a null is present the result of the calculation is null, and therefore you see nothing in your Viz.

               

              My two solution options get around this problem differently. The easy one is just using Tableau's ZN() function, which returns a 0 anytime it sees a null. This allows your calculation to work as you originally expected.

               

              The other option was aggregating SUM([Date's Sales]) and SUM([Date-1 Sales]) before carrying out the subtraction. This causes tableau to sum up all of the values within each column, and then carry out the subtraction. (summed up column vs row level calculation)

               

              In summary, there are many ways to get to your desired end result:

              1. Add an ELSE clause to [Date's Sales] and [Date-1 Sales]

              2. Provide Null handling in the form of ZN() wrappers in your [difference] calculation

              3. Aggregate your measures before carrying out your subtraction

               

              For more information around row level vs aggregated calculations I highly recommend taking a look at this free Tableau training video: Aggregation, Granularity, and Ratio Calculations | Tableau Software

               

              -Ryan