5 Replies Latest reply on Jun 28, 2013 3:40 AM by Marek Gutowski

    quick table calculation - fix the first value

    Marek Gutowski

      Hi all,

       

      I am using standar Tableau difference to calculate difference between current and previous value:

      ZN(SUM([Cost])) - LOOKUP(ZN(SUM([Cost])), -1)

       

      How would I modify the formula so the first value would remain unchanged.

       

      Thanks

        • 1. Re: quick table calculation - fix the first value
          Shawn Wallwork

          Try: LOOKUP(SUM([Cost]),FIRST()) - LOOKUP(ZN(SUM([Cost])), -1)

           

          That'll 'lock' in the first row. If you want some other row, the second for instance it's:

           

          LOOKUP(SUM([Sales]),FIRST()+1) - LOOKUP(ZN(SUM([Sales])), -1)

           

          You can also put it in terms of the last row:

           

          LOOKUP(SUM([Sales]),LAST()) - LOOKUP(ZN(SUM([Sales])), -1)

           

          --Shawn

           

          You actually might find this formula a bit more helpful:

           

          LOOKUP(SUM([Sales]),FIRST()) - LOOKUP(ZN(SUM([Sales])), 0)

           

          By not looking at the previous value in the second clause, the values aren't calculated for the line before. So instead of this:

           

          FIRST-1.png

           

          You'll get this:

           

          FISRT-2.png

           

          Which is showing the difference from the first to the current row. Personally I think you should also switch the clauses so it shows the amount of growth from the first year to the current year in positive number.

          • 2. Re: quick table calculation - fix the first value
            Marek Gutowski

            Hi Shawn,

             

            Thanks for your your suggestions but none of the formulas have returned the result I need. It might be that my data is structured differently to what you expect.

            Please note: the original formula: ZN(SUM([Cost])) - LOOKUP(ZN(SUM([Cost])), -1)

            works fine except for the first period which needs to be locked.

            • 3. Re: quick table calculation - fix the first value
              Shawn Wallwork

              The reason it doesn't show anything on the first line, is because there is no previous value to subtract. If you want to fill in that box you can write something like this:

               

              IF FIRST()=0 THEN ZN(SUM([Cost]))

              ELSE ZN(SUM([Cost])) - LOOKUP(ZN(SUM([Cost])), -1)

              END

               

              But you're going to confuse your viewers, and you're telling a bit of a data lie.

               

              --Shawn

              • 4. Re: quick table calculation - fix the first value
                Marek Gutowski

                Thanks Shawn

                 

                This is where i get confused, I have the data presented regionally, with cost by month. The original formula doesn't show anything for the first month just for one region. The rest of the regions have values calculated for the first month (they are mostyly high negative values). I don't understand how those values got calculated. Is there a way to find out what data is used for the calculated value (what is the data behind it)?

                 

                I only filter for 2013 data but I also have 2012 and 2011 stored in the database (I wouldn't expect them to be used in the calculation though).

                • 5. Re: quick table calculation - fix the first value
                  Marek Gutowski

                  I can see what's been happening. I've had both "region" and "month" in the column space. When the value for the next region was displayed for the first month, the total value for the previous region has been deducted.

                   

                  I have moved region to the row section and the values are calculated correctly.

                   

                  Shawn, thanks for your help.