Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

4 Replies
sunny_talwar

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

swuehl
MVP
MVP

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:

LOAD

     [sales rep.],

     Max(amount) -Min(amount) as Delta,

     FirstSortedValue(amount, -date) as LastAmount,

     Avg(amount) as Average

RESIDENT YourTable

GROUP BY [sales rep.]; 

Not applicable
Author

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

sunny_talwar

May be using this script:

Table:

LOAD * Inline [

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:

LOAD *,

  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:

LOAD Day,

  Avg(Value) as AvgValue

Resident Final

Group By Day;

DROP Table Table;


Capture.PNG