Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Regards
Arif
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.
Hi Gysber,
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.
Regards
Arif
Calculate the HoursGrowth at the lowest level and aggregate as needed in your charts and (pivot)tables.
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
Regards
Arif
For example, If I simply aggregate the consultant hours growth, it gives me 170 while the growth at one level up should be 95.
Regards
Arif
We're probably misunderstanding each other. Can you post the qvw file your working on?
Hi Gysbert,
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
Regards
Arif
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.
I am getting even stranger result than previous after I updated the script as you instructed. Are you also getting the same result?
Arif