topic Forecast to month end in QlikView

Forecast to month end

Wed, 20 Apr 2011 16:24:53 GMT

Hi,

I hope someone can help me with some advice.

I have data collected each day and using this I would like to know what the forecast is likely to be at the end of the month. So at the moment I have a figure of 48 I know if I divide this out by day using the 20 days of april so far I get 2.4 per day and then * 30 days in month and would expect to have a figure around 72 at the end of the month. But I am not sure how to show this in either a table or a chart so it alters each day.

Any help would be appreciated

Thanks

Forecast to month end

Wed, 20 Apr 2011 16:36:19 GMT

Try this expression

Forecast * (day(monthend("Month_Yr"))/(day(today())))

48 *(30/20) = 72

Forecast to month end

Miguel_Angel_Baeyens — Wed, 20 Apr 2011 16:39:07 GMT

Hello,

It depends on the analysis and the chart you want to use (even if you want to get it done at script level), but the expression would be something like

(Sum(Amount) / Day(Today())) * Day(MonthEnd(Today()))

Thus you get first the amount per day, then per month (multiplied by the number of days in the month).

Hope that helps.

Forecast to month end

Thu, 21 Apr 2011 09:58:50 GMT

Hi,

Thank you Miguel that works perfectly for the current month. Could I ask you one more question about this.

When the month is incomplete this works perfectly but I would like it to be dinamic to the month the user selects. So if the month is incomplete I use the formula you gave me but if it is complete then use the full data. I just can't think how to get this to work with the calendar.

Thanks again for your help

Incomplete Month Formula:-

(

Sum({<Performance_Category={'RED8'}>} IF(FirstA8MinRespTimeDuration>1800,1))) / Day(Today()) * Day(MonthEnd(Today

()))

Complete Month Formula:-

sum(Performance_Category={'RED8'}>} IF(FirstA8MinRespTimeDuration>1800,1))

Forecast to month end

Miguel_Angel_Baeyens — Fri, 22 Apr 2011 18:27:58 GMT

Hi,

You will need to use a conditional to see whether the selected month is complete or is not. Say you already know in your script when is your last date and you store that information into a variable. The expression should look like a combination of the two you mention above

Sum({< Performance_Category = {'RED8'}, FirstA8MinRespTimeDuration = {'>1800'} >} If(Max(Date) = $(vVariableWithMaxDate)), 1 / Day(Today()) * Day(MonthEnd(Today())), 1)

Depending on the chart you are using, I'd do two different expressions that will show only in one of both cases (current month not finished or complete month).

If your chart manages a huge amount of records, the performance will degrade.

Hope this helps.