Discussion board where members can get started with Qlik Sense.
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 ??
Are you trying to do this in the script or on the front end?
Maybe like this in a chart with dimension sales rep.
date field needs to have a numeric representation to be used in FirstSortedValue() as sort weight
In the script, it would be pretty much the same:
Max(amount) -Min(amount) as Delta,
FirstSortedValue(amount, -date) as LastAmount,
Avg(amount) as Average
GROUP BY [sales rep.];
I'm trying on the front end because i'm beginner !! but I can try any mode.
May be using this script:
LOAD * Inline [
Day| Name| Value
day 1 | John | 4
day 1 | Alan | 6
] (delimiter is |);
If(Name = Previous(Name), Peek('Value'), 0) as Previous_Day_Value,
If(Name = Previous(Name), Value - Peek('Value')) as Delta
Order By Name, Day;
Avg(Value) as AvgValue
Group By Day;
DROP Table Table;