Generating Missing Data In QlikView

[hic]

• What techniques are there to generate “new” tables; tables that don’t exist in the source data?
• How do I loop over one and the same record in source data?
• How do I propagate a value from above record to the current one?
• How do I populate a sparsely populated field?
• How do I generate all combinations – the Cartesian product – of two or more fields?
• How do I make simulations in QlikView?

These questions and others are answered in this Technical Brief.

HIC

[#2]

Excellent article, thank you very much for taking the time to put this together.  I was hoping there was an example of what I am trying to achieve, and it may be there but just not specifically.  My situation is that we have a return on investment calculation for R&D projects with forecast sales in terms of whole fiscal years.  Post launch, we want to look at this as a monthly revenue achievement vs. forecast so I'm assuming I will need to prorate the annual forecast value into a monthly figure for comparison.  Obviously this data doesn't exist as there is only one value per FY. To further complicate the issue, each project will have a different launch date and a difference life cycle projection.   Projects can launch at any point in the year and have anywhere from 5 to 15 years of projected revenue.  How would you approach this problem? 

[hic]

Forecasting is a complicated problem, and I am not sure that generating data in the QlikView script is the right way.

It's possible, but it will be complex, and perhaps still not solve all your requirements. For instance: How many dimensions do you have? (e.g. Calendar, Products, Customers, Business units, Market campaigns, Projects, etc.) And do you want to be able to use all these dimensions? If so, you may need to generate all combinations - the Cartesian product - of all of them. This will be complex: You need to estimate the YoY increase per product, per unit and per region separately. Also, the data amount will become large.

If budgeting and forecasting is important, it may be a good idea to look at dedicated tools for this.

HIC

[#4]

Its very nice document.

[chrisg]

very usefull!

[#6]

Very well explained.

Helped me a lot!!!

Thanks

[#7]

very helpful

[Anonymous]

Hi Henric

I have used a combination of Canonical Calendar script and 'Generating Missing Dates in Qlikview' to create 2 different calendars as I need to show missing ticket count for the dates that don't exist.

As a result some of the graphs take almost 10 seconds to load. Users will not wait that long. Most of the formulas are written in the Expressions so I tried loading these formulae through a spreadsheet just in case it tries to improve performance. Can you please suggest what I may try to do to improve the performance? I haven't used set analysis in the expressions. These are more based on if clauses.. Not sure if this impact the performance for the charts..P

[ahaahaaha]

Thank you. A good document.

[bullish35]

There may be a variety of factors impacting your performance, but as a general rule replace your IF statements with set analysis. Also recommend using flags wherever possible (1,0) . Good luck!

[hic]

As ellenblackwell‌ suggests, you should use Set Analysis instead of If()-functions. (If()-functions are VERY CPU-demanding.)

Further, any calculation that doesn't change when you make selections should be made in the script and stored as a separate field. E.g. flags like IsCurrentMonth, IsInYTD, and numbers like Month, WeekNo, DayOfYear, etc. This will use a little more memory, but will decrease the amount of CPU-time needed for each click.

Storing your formulae in a spreadsheet will not affect performance.

HIC