0

I am working on a cumulative dataset and I need to convert cumulative values to daily values of the same column. I did it with creating a calculated field, but, the first row's value is missed. Because there is no previous row for the first row. I need to write the code in a way that lets the first row by it's own value. my Code is this :

ZN(SUM([Total Ground Weapons])) - LOOKUP(ZN(SUM([Total Ground Weapons])), -1)

Ayda
  • 3
  • 3

1 Answers1

2

Samkart is broadly leaning in the correct direction, though I'd probably go with a logic statement that utilises Index() rather than First()

First(), Last() and Index() are all row sequencers:

  • Index() enumerates from 0 incrementing by 1 for each record in the partition
  • First() enumerates from 0 decrement by 1 for each record
  • Last() is an odd one, beginning the very last record in the set as 0 and then incrementing by 1 until you reach the first record in the set eg 17 rows in the set, Last() would begin as 0 incrementing by 1 counting to 16 on the first record

So for your calculation I'd test:

If Index() = 1 Then Sum(0)
Else Zn(Sum([Total Ground Weapons])) - Lookup(Zn(Sum([Total Ground Weapons])), -1)
End

This way, you'll be able to define a value for the first entry in your set.

Steve

Steve Martin
  • 113
  • 7