7 Replies Latest reply on Dec 15, 2019 10:12 AM by Bryce Larsen

    WoW Calculation not giving % change value

    Puja Doona

      I was trying to get the WoW change for the attached data, but my change calculations are not working. Workbook attached. Any guidance on what could be wrong?

        • 1. Re: WoW Calculation not giving % change value
          Bryce Larsen

          Hi Puja,

          What is your goal for you final view? Is it a table? Typically using a table calculation will be easiest, but depends on your viz.

           

          Right now it's not work because your expressions are only returning the week number within the LOD and then comparing at the row level, rather than assigning "prior week sales" to each row - which is what the LOD would need to do. But even with that you'd need to have all of your filters added to context.

           

          Table calc method: How to Calculate Week over Week Change of the Last Complete Week - YouTube

           

          Best,

          Bryce

          1 of 1 people found this helpful
          • 2. Re: WoW Calculation not giving % change value
            lei.chen.0

            Hello Puja,

             

            Please be careful with data granularity and Tableau's Order of Operations - Tableau

             

            In your calculations [Last Week Sales] and [Prior Week Sales],

             

            In your data, { MAX(DATEPART('week',[Order Date])) } is a constant 53.

             

            As a result you get this for 2016 (no value for Week 53).

             

            Then, data granularity.

             

            In the same screenshot,

             

            For Week 51 row, Prior Week Sales is 25,577, Last Week Sales is Null, the results for change and % Change give Null too.

            The same for Week 52.

             

            Instead of LOD, difference calculation is the typical use case of table calculation, like this.

             

            The sample workbook is attached.

             

             

            Regards

            Lei

            • 3. Re: WoW Calculation not giving % change value
              Mike Cheng

              Hi Puja

               

              Because the calculation you did was at the row level, you need to sum it otherwise tableau cannot find the number to do the calculation

               

              and for the viz, because the calculation will look for the numbers in the column but there is no numbers but itself in these column,

              therefor you need to get rid of the week pill for your calculation to work

               

              alternatively, it will be easier if you use the build in "quick table calculation" feature to do the job

               

              hope this help

              mike

              • 4. Re: WoW Calculation not giving % change value
                Puja Doona

                Thanks @mikecheng this is what I wanted to do. I could have done regular table calculations but I wanted to get the summary in one line for the last week only. Some thing like this:

                 

                Also, thanks Lei Chen @brycelarsen, but I was looking for the solution Mike suggested.

                 

                On similar, note, I wanted to do the MoM calc, Last Month, Previous Month and Same month LY. I'm still struggling with Same Month LY part. Does this part differ from WoW logic?

                • 5. Re: WoW Calculation not giving % change value
                  Bryce Larsen

                  Hi Puja,

                  You could still get it all in one row using table calculations.

                  Using LOOKUP(SUM([Sales]),-1) you get it on the last row but for the prior week. Likewise for % change, you simply do:

                  (SUM([Sales])-LOOKUP(SUM([Sales]),-1))/LOOKUP(SUM([Sales]),-1)

                   

                  That puts it all on one row and then you can also keep the Week NBR in your viz. You can add LAST()=0 (calculated on Week NBR) and filter on TRUE to only show the "current" week.

                  This is much more flexible and efficient than using LOD expressions.

                   

                  Best,

                  Bryce

                  1 of 1 people found this helpful
                  • 6. Re: WoW Calculation not giving % change value
                    Puja Doona

                    Hi @Byrce, Thanks that's helpful. Learning a quicker way helps.

                     

                    And apologies I realised I shared the wrong snapshot. What I want to do is this, that will go on a dashboard - basically inserting the numbers and change % to be displayed in "text", because everyone just wants the summary right

                     

                     

                    I don't think I could use the table calcs for this but not sure?

                    • 7. Re: WoW Calculation not giving % change value
                      Bryce Larsen

                      Hi Puja,

                      You can simply put Measure Names on the Rows shelf next to the Week NBR instead of the columns shelf.

                      Alternatively, you could even put in in the Text pane as well.

                       

                      Best,

                      Bryce