I currently calculate the values of multiple storerooms by a Facility Number. Here is my script:
Value:
LOAD
FacilityNumber,
MonthStart(OrderDate)as OrderDate,
Sum(ExtendedCostPrice) as Value
Resident Value_Temp
Group by FacilityNumber, MonthStart(OrderDate);
Left Join (Value)
LOAD FacilityNumber,
OrderDate,
Num(If(FacilityNumber=Peek(FacilityNumber), Value+Peek(RunningTotal), Value), '$ 0,000') as RunningTotal
Resident Value
Order By FacilityNumber, OrderDate;
This works ok, assuming you have at least one transaction per month.
Now I'm being asked to do the same calculation but by Facility AND Item. I tried this:
Value:
LOAD
FacilityNumber,
ItemNumber,
MonthStart(OrderDate)as OrderDate,
Sum(ExtendedCostPrice) as Value
Resident Value_Temp
Group by FacilityNumber, ItemNumber, MonthStart(OrderDate);
Left Join (Value)
LOAD
FacilityNumber,
ItemNumber,
OrderDate,
Num(If(FacilityNumber=Peek(FacilityNumber) and ItemNumber = Peek(ItemNumber), Value+Peek(RunningTotal), Value), '$ 0,000') as RunningTotal
Resident Value
Order By FacilityNumber, ItemNumber, OrderDate;
The problem I'm having is what I mentioned above, if you don't have at least one transaction per month, you get 'gaps' in the data where there is no running total for that month.
Here is an example of the problem:
| Item Number | Facility Number | Running Total | Revenue | Order Date |
| 466088 | 4506 | $744 | $744 | 4/1/2015 |
| 466088 | 4506 | $744 | $0 | 8/1/2015 |
| 466088 | 4506 | $992 | $248 | 1/1/2016 |
| 466088 | 4506 | $744 | ($248) | 2/1/2016 |
| 466088 | 4506 | $0 | ($744) | 3/1/2016 |
| 466088 | 4506 | $992 | $992 | 4/1/2016 |
| 466088 | 4506 | $1,239 | $248 | 5/1/2016 |
| 466088 | 4506 | $744 | ($496) | 8/1/2016 |
| 466088 | 4506 | $248 | ($496) | 9/1/2016 |
| 466088 | 4506 | $248 | $0 | 1/1/2017 |
| 466088 | 4506 | $496 | $248 | 2/1/2017 |
| 466088 | 4506 | $496 | $0 | 3/1/2017 |
As you can see if the user selects a month in between the months highlighted in red (Like 12/1/2016 or 9/1/2015), they get zero for the running total, which is incorrect.
How can I modify this script so that it 'fills in' the missing months with the correct running total ? I've reviewed posts about generating missing data on this site but I can't find an answer for this specific example.
Don
