I have a dataset containing Opportunity Start Date, Opportunity End Date and Volume columns.
Client has asked to show a visualisation in the Dashboard where volume is distributed between Opportunity Start Date and Opportunity End Date month by month on equally basis throughtout the whole year.
For example, for below datapoints.
S No || Opportunity Start Date || Opportunity End Date || Volume
1 || 01-01-2023 || 31-12-2023 || 1200
2 || 01-06-2023 || 31-12-2023 || 2400
There will be 12 bars in the visualisation showing [ 1200 * 5/12 ] = 500 each for Jan to May
& [ 1200 * 7/12 ] + [ 2400 * 7/12 ] = 2100 each from June to Dec month.
Is there a way this can be achieved using SAQL?
Alternatively I would have to make the column to row transformation in Recipe by splitting each Opportunity row into 12 rows each for each month and divding the volume eqully.
I managed to do this in recipe. For simplicity sake I created 12 rows for each record irrespective of the start date and end date and created a new column 'Sub Volume' which is set to "0" or "Volume / No of months between start date and enddate" depending on the start date and end date.