Struggling with end-of-month prediction (set analysis)

[#1]

Hi,

I've recently learned how to use set analysis in QlikView and I'm faced with a slight problem.

From previous experience I've shown that predicting end of month is best done by comparing the last 30 working days and then extrapolating to end of month using the number of remaining working days.

Clearly, someone else has thought this also, since there's a NetWorkDays() function in QV.

I've created a text object that has this statement:

= Sum({< Date = {">=$(=Date(MonthStart(date(today())), 'YYYY-MM-DD'))"} * {"<=$(=Date( date(today()), 'YYYY-MM-DD'))"}>} Sales) +

( Sum({< Date = {">=$(=Date(date(today())-30, 'YYYY-MM-DD'))"} * {"<=$(=date(date(today()), 'YYYY-MM-DD'))"}> } Sales) / NetWorkDays(date(today())-30,date(today())) *

NetWorkDays(date(today()),MonthEnd(date(today()))))

What I'm trying to do, is count the sales this month and add them to an estimate built by comparing the sales and workdays of the past 30 days vs the remaining workdays this month.

Here's a short load script that will set the stage: http://pastebin.com/raw.php?i=Wm2RB5th

The problem is that I can't seem to make this shape up like I want it to, I would like to see a graph where one line represents the prediction done, that day, using this formula and a bar showing the total sales so far that month ( if all is good, the two will meet at the end of the month ).

I've attached a file where I've done this, I'm assuming I'm doing something wrong with the "scope" of the set analysis, even though i'm trying to make a calculation based on history it's only using the Date's Sales and not the totals like i'm trying to. Or then I dont understand how dimension works properly..

Thanks in advance for your help!

[lironbaram]

hi

attach is your file

i changed the expressions a little bit

the projection i added the total part to the sum parts of the expression

and in the MTD i checked the full accumulation.

hope it helps you

[#3]

Hi Liron,

Unfortunately this does not do what I'm trying to get. TOTAL basically seems to eliminate the set analysis rules and draws only one flat prediction line although the prediction varies from day-to-day ( as you can see if you slide the vProjection Slider back and forth and reading the projection number).

I'm trying to get QV to draw me a "day by day" MTD and Prediction that day so I can see how the prediction is evolving, is it being overly optimistic in the beginning of the month or pessimistic?

Please see in this fake example what I'm trying to make it look like:

[mikkeltaylor]

HI John, did you ever manage to find a solution to this? as it's something I will need to attempt soon as we'll.