5 Replies Latest reply on Sep 12, 2018 12:04 PM by chris monger

    What do these formulas mean ?

    Gavin Wong

      Hi Community,

       

      i inherited a sheet and am trying to find out what some calculated fields are doing.

       

      DATEDIFF('day',ATTR([Date]),TOTAL(MAX([Date])))=0  <---- this calculated field is named as "Current"

       

      any idea what the above does ?

      Date is a column filled with Dates. in the yyyy-mm-dd format.

       

      while i know what does attr does , but when put together in the above formula, i am lost.

       

      And then theres this...

       

      ZN(LOOKUP(WINDOW_SUM(IIF([Current],ZN(SUM([Sales])),NULL)),0))

       

      as what i learnt, iif is used this way

       

      iif(year[order date]=2017, [sales],0)

      so why does the above iif not checking against something ?

        • 1. Re: What do these formulas mean ?
          chris monger

          Hi Gavin,

           

          The first section is looking to find a field with the max date in the data set. As it's a table calc it depends on how the data is partitioned, but i would guess that it is in view with the dates as a dimension, so that the attr([date]) shows the date for that day, otherwise it would error with more than one option.

           

          The reason the iif statement doesn't look to be checking against anything is that [current] is a boolean field, if the date is the max then it will be true, otherwise it will be false, so it is performing the calculation in the [current] field within that statement.

           

          This is then returning the total of sales for the max date in the view.

          • 2. Re: What do these formulas mean ?
            Gavin Wong

            Hi Chris,

             

             

             

            see attached simple worksheet.... trying to understand the datediff of the order date and the total of max order date...

             

            seems all 0 ?

             

            if i've used Year, then those order dates in 2015 should be 3 right ? why does it show 0 ?

             

             

             

             

             

            Gavin

            • 3. Re: What do these formulas mean ?
              chris monger

              Hi Gavin,

               

              It's because the table calculations for your total max order date and datediff fields are at different levels.

              The date diff is set to be at table(across) level, so is only picking up the max date for each row, which is the order date. If you change this to table(down) then you get the 3 years you are expecting.

               

              In this screenshot I've switched the table calc setting around.

              • 4. Re: What do these formulas mean ?
                Gavin Wong

                Hi Chris

                 

                Yes it works. But i dont get it. In my original file, for example Aaron Bergman,  order date 18 Feb 2015, so what did it minus to get 0 for datediff ? did it subtract itself ?

                 

                As for total Max, what went wrong?

                 

                I think i get it but yet i dont... could you elaborate what went wrong with with the datediff and total max date table calc ?

                • 5. Re: What do these formulas mean ?
                  chris monger

                  The way the field is set up is only calculating the max date from that row, table functions can calculate on different ranges, from just the active row to the entire table.

                  This should give you an overview of table functions

                  Transform Values with Table Calculations

                  1 of 1 people found this helpful