Working with Monthly Targets and different Locations

[#1]

Hi,

I am trying to put together a chart that analyzes targets and actual results.

I have two tables right now, my sales data and my master calendar. The third table I would bring in would be the targets.

Here is where my issue begins...my sales data covers multiple locations. Let's say 1, 2, 3.

Each location has its own Targets set for the year and they vary from Month to Month. (The analysis would be done by month and quarter)

So when creating my links, I would have one link that connects to the Sales Data (for the locations) and one link that connects to the Master Calendar (for the Months). This creates a loop and is no good.

How do you recommend I go about this? ....

To further complicate the matter, each location has multiple divisions and they too have differing targets by month, so this would be another table....

Thanks for any help!

[pover]

Concatenate the actual results table with the targets table so that they share the same filters. When you concatenate the tables add an extra column that will tell you if the row is an actual result or a target. When you make the formulas then you can use set analysis like this:

sum({$<DataType={'Target'}>} Amount)

or

sum({$<DataType={'Actual'}>} Amount)

Regards.

[#3]

My actual table has about 20 columns and about 4 million rows. (Covers data over the last 4 years)

Will concatenating this be efficient? How do I add an extra row during concatenate ( I am not really familiar with this function)

Thanks,

[pover · Accepted solution]

I suppose it depends on your system, but it will have better performance than using a date island and if you are able to use the same columns between the target and actual table, the application will not increase too much in size since QlikView compresses the duplicate values found in the same column. This has been my strategy for the past couple years after trying to use link tables or table islands and it has worked well for me. It is basically the same principle used to create a datawarehouse.

To add a new column do something like below:

Load 'Target' as DataType, //New column
Date,
Location,
Division
....
From ....

Concatenate

Load 'Actual' as DataType, //New column
Date,
Location,
Division
....
From ....

Regards.