1 Reply Latest reply on Jan 17, 2016 5:19 PM by Shinichiro Murakami

    Comparing Data from Different Rows

    Mike Waldren

      Hey everyone,


      Something I have wrestled with for a few hours is obtaining the following output from the below data source into a simple table:



      - Data not sorted in data source

      - Multiple rows for Date-ID combos (see yellow below, needs to be summed)


      I would ideally like to create a calculated field for each row in the data source so that simple calcs can be done.

      i.e: for each ID - (Value of Current Week -minus Value of Current Week - 1)

        • 1. Re: Comparing Data from Different Rows
          Shinichiro Murakami

          Mike,  Yes, you can.


          Assuming your date is only have one day per week.

          If not still can be done, but I simplified,  If you can share the sample workbook with packaged, I can modify it.




          Concept is get current week value and last week value by formula respectively

          Calculated the delta of these.

          Use parameter to specify "Current Week"


          == Steps ==


          *  Create parameter as "Date" as List added from "Date" Filed.


          *  Add Three Calculated Fields:


          [Current Week Value]

          if [Current Week]=[Date] then[Value] END

          If you want to put  "0" to Null value,  if [Current Week]=[Date] then[Value] else 0 END


          [Last Week Value]

          if [Current Week]-7=[Date] then[Value] END

          If you want to put  "0" to Null value,    if [Current Week]-7=[Date] then[Value] END


          [Current - Last]

          sum([Current Week Value])-sum([Last Week Value])


          To get this results, you need to remove "Date" from column/row because sum calculation for each "Date" resulted in "Null"

          and you can not get correct delta in "Current - Last".  ==> See "Sheet 1 (not work)"

          Version 9.0 is attached.