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

# Comparing Data from Different Rows

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

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