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:

       

      Challenges:

      - 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.

           

          Anyways,

           

          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.

           

          Thanks,

          Shin