Creating a table out of random facts in QlikView

I have run across this a number of times where you have a number of independent calculations needed to create a table of values. Some of the time you run into this as you are pulling data from multiple sources and trying to combine them in a single place.

I had one recently where it was a sales forecasting application.

The sales targets are fixed values, that are just variables in QlikView. With three different targets and two data sources for the sales data – how do you get this all in one table?

There are two components to the solution.

1. For the dimension of the table you use the ValueList function.

ValueList(‘Product’, ‘Service’, ‘Parts’)

This gives us 3 rows for our straight table

2. Now for the fun part – how do you do expressions?

The answer is to use an if statement and use the RowNo function

So – for the Goals, the calculation just becomes

= If(RowNo()=1, [Product Goal], If(RowNo() = 2, [Service Goal], [Parts Goal]))

Following that model, create your additional columns, and add totals, cross column calculations, etc.

One potential thing to trip over is that for some reason, copy and pasting the entire expressions does not work in QV 10. They seem to copy properly, but then they do not display. 

Once i got it working – it was one of those things that is so obvious you wonder why you never thought of it before….

I hope i saved you a few hours there..