Altering future predictions using IF Statement

[#1]

Hi All,

I am trying to create a graph that shows revised predictions based on new results. I have made a predictions for hourly call volume rates for the day. I then want QlikView to alter this prediction based on actual call volumes.

In this example I have assumed that actual is 75% of predicted. This could vary and so I need QlikView to calculate the average change and use this to change the prediction. I struggled with this so manipulated the data in Excel to do this for me.

My problem occurs when the call volume is 0 (i.e for those hours in the day that have not occurred). I need it to use the average of previous hours rather than work out the change from 0 (which is 0). So far I've got:

If([Today]=0, Sum([Line Predictions])*Avg([Average Change]), Sum([Line Predictions])*Sum([Today])/[Line Predictions])

Apologies for the confusing explanation. I have attached my sample data and QlikView file to explain things better.

Thanks in advance

James

[#2]

It wouldn't allow me to attach 2 files so here is my excel spreadsheet with sample data

Thanks

James