Set analysis & forecasting question

[#1]

Hi everyone,

Please consider the following:

1. 2009 MTD Picks = Last year's picks for the relevant month
2. Picks = This year's picks for the relevant month - sum([VALID PICK])
3. Working days in month = The amount of working days this year
4. Projected daily = A forecast figure: Picks 2010 / Working days in month. If isnull([PICKS 2010]) then the figure is equal to the sum of projected daily for the last 3 months / 3 (Eg - January = 4578 / 22 and November = 205+207+201 / 3
5. Projected monhtly = Projected daily * Working days in month

I'm having problems with the following:

• For 2010 I only have data up until June, but [Month] should always be from January - December irrespective of year or month selected.
• Could someone help me perhaps with the set analysis for the 2009 MTD picks figure?
• Projected daily - How to calculate the 3 previous' months when there is no data for [PICKS 2010]

I think if someone could please help me with the above I should be able to complete the rest to finish my model.

Any suggestions on making things simpler would also be appreciated alot.

Thanks!!

[#2]

If anyone is able to give me a clue as to how to do this or let me know whether it's possible or not please let me know you're inputs will be greatly appreciated 🙂

Happy Qliking!

[#3]

Everything you've described should be possible in QlikView.

I believe that once you define the set for 2009 MTD picks, you'll solve the missing months issue.

The easest way to get the set for your MTD 2009 picks would be to ensure that you have a 'year' field associated with the picks, and then:

Sum({$<PickYear = {'2009'}>} [Valid Pick])

Optionally, you might also want to make it dynamic, as opposed to hard coding the year.

Sum({$<PickYear = {$'=year(today())'}>} [Valid Pick]) (sorry, my syntax is likely wrong, dollar sign expansion and I are not good friends… But you get the idea.

The projected daily is just more of the same. Inside an if, you would resolve your if statement, and if picks 2010 contains nothing, then you could either re-calculate out the projected daily using set analysis, which would be nasty… or you might be able to internally reference the table using column or row functions. I haven't ever tried to do this personally, but I imagine it would work. They may also be another way to do it, using 'pick' or something like that.