calculate difference between two snapshots values using expression
Hi All,
I have a table which has these columns. Snapshot Date, Consultant, Customer, Snapshot Type, Total hours, Hours Growth. The snapshot is taken twice a day, so snapshot type is AM if snapshot is taken in morning and is PM if snapshot is taken in evening. Total Hours is the number of hours the consultant is supposed to work with customer in next one month. Hours growth is the difference between two consective snapshots Total hours. How can I take this difference using expression in line chart, straight table and pivot table. The growth is not simply a difference between current row and previous row, we also have to consider that the growth is being calculated for every same pair of consultant and customer.
I think you can do this by first adding a field HoursGrowth in the load script. The data needs to be sorted first so the records for consultant-customer combinations are ordered such that the peek function can be used.
load *,
if(peek(Consultant)=Consultant and peek(Customer)=Customer,[Total Hours] - peek([Total Hours])) as HoursGrowth
resident xxxtable order by Consultant,Customer,SnapshotDate,SnapshotType;
Once you have the HoursGrowth field filled correctly you can use it in charts.
Thanks a lot for your quick response. I want a dynamic formula that can calculate it at any detail level. In our case, the growth at snapshot date and snapshot level (AM/PM) is different than the sum of growth for individual consultants days growth. For the first row of each consultant, the total hours and hours growth will be same as their will be no previous row for first row. While if we analyse this at Date,AM/PM level, it should be the difference between total hours at present snapshot - total hours at previous snapshot regardless of who was the consultant. for example
Snapshot Date
Snapshot Type
Consultant
Total hours
hours growth
23-10-2012
AM
Arif
23
23
23-10-2012
AM
x
25
25
23-10-2012
AM
y
27
27
23-10-2013
PM
bbb
36
36
23-10-2014
PM
xdf
45
45
23-10-2015
PM
ert
89
89
Now If you check the growth at PM. The growth is correct at consultant level, but If we want to show the same growth at snapshot date and snapshot type level, the growth is not the sum of individual consultants growth but again it is the difference between sum of hours at one row - sum of hours at previous row. The growth at snapshot date and snapshot type level should be following.
Snapshot Date
Snapshot Type
Total hours
hours growth
23-10-2012
AM
75
75
23-10-2013
PM
170
95
How can I resolve If I want to calculate it at one level up of consultant level.
as I have explained in my above details, simple aggregation gives incorrect result. In the above two tables that I have shows....aggregating the growth at date level is different than taking the difference at the up level. How can I balance the growth of once consultant against another If I want to calculate at one level up
Here is a sample application file. I have identified the issue in attached image. I have identified one row issue,but actually in the chart that I have highlighted all growths are incorrect at snapshot date and snapshot type level. second row should have a growth of 17, third one should be -19 and fourth one should 11 respectively. Currently they are 11, -10 and 0 Please feel free to ask me if you are not able to understand the issue
I see what happens. If a consultant appears for the first time we don't take the hours into account, only the next records are used in calculating the growth. That's indeed wrong. So we need to change the calculation so it uses Total Hours is there is no previous record:
if(peek(Consultant)=Consultant,[Total Hours] - peek([Total Hours]),[Total Hours]) as HoursGrowth
Once you change that and reload you'll see the total Hours Growth for 22-10-2012 is 17 like you expect.
