4 Replies Latest reply: May 12, 2016 1:36 PM by Sunny Talwar

# calculation of delta

I have a table with 3 columns: date, sales rep, amount.

Example:
day1 | John | 4

day1 | Alan | 6

day 2| John | 5

day 2 | Alan | 9

day 2 | Mary | 4

.....

I need calculate the delta of each sales rep, including the amount of the last day and the average every day.

The result may be another table or a crosstable.

Could you help me, please ??

• ###### Re: calculation of delta

Are you trying to do this in the script or on the front end?

• ###### Re: calculation of delta

I'm trying on the front end because i'm beginner !! but I can try any mode.

• ###### Re: calculation of delta

May be using this script:

Table:

Day| Name| Value

day 1 | John | 4

day 1 | Alan | 6

day 2| John | 5

day 2 | Alan | 9

day 2 | Mary | 4

] (delimiter is |);

Final:

If(Name = Previous(Name), Peek('Value'), 0) as Previous_Day_Value,

If(Name = Previous(Name), Value - Peek('Value')) as Delta

Resident Table

Order By Name, Day;

AvgPerDay:

Avg(Value) as AvgValue

Resident Final

Group By Day;

DROP Table Table;

• ###### Re: calculation of delta

Maybe like this in a chart with dimension sales rep.

Three expressions

1)

=Max(amount) -Min(amount)

2)

=FirstSortedValue(amount, -date)

3)

=Avg(amount)

date field needs to have a numeric representation to be used in FirstSortedValue() as sort weight

edit:

In the script, it would be pretty much the same: