sales vs targets - giving wrong target values

[louwriet]

Hi All,

I need urgent help with target figures that is not displaying
correct figures. I have targets in a file per type , country, product and year
month and this is also my key to my transactions table where I have sales per
day.

My problem is when I create a pivot that look at sales for
the month per  type, country and year it
pull through the wrong target and the reason being is that if there was not a
sale for a specific product that day Qlikview does not take that target in consideration.
It is actually oblivious because product is part of the key to the target file.
How can I get pass this issue – if I want to look at sales and targets for a
month per type(Export) country(Namibia) I want to look at total target for
Export Namibia for month 201301 and not just at the targets of the products
that there where sales for.

My expression for my target is sum({<Year Month={"$(=max({<Year Month=>}
Year Month))"}, Type = {'EXPORT'}>}[Target])

please see my attached excel spreadsheet explanation.

Much apprecaited

Regards

Louw

[michael_anthony]

How have you constructed your tables?  do you have a Target table and an Transaction (FACT) table which has a multi-field key (Product, Country, Type, YearMonth) which joins them AND then a separate table for Date?

It's because the Date selection is passing through the FACT table to get to Target that it loses the links.  Why don't you instead concatenate your Target and Fact tables together into one table - the dimesions are mostly common to them anyway.  That would remove your problem.

It would create another that your Targets are by Year Month, but your Transactions and Date table are linked by day.  In our implementations we usually spread our Targets across the days in the month so that both Sales FACT records and Target records are by day.

[#3]

Hi,

Please see the attached file..

Hope it will helps!!

//

Chandra

[michael_anthony]

I could see a couple of issues:

In the script you are Joining the FACT and Target tables, believe it would be better to CONCATENATE the tables.  Join in this instance would be a very dangerous method to use.

In the chart you've got Target as a Dimension, rather than an Expression.  If you change to Concatenating the tables, then using normal SUM(Target) as the expression it should work.

[#5 · Accepted solution]

Hi,

see the attached file for concatenate between tables..

If you use concatenate it will get append the data means will add record by record but it will never form complete record with all the fileds..

if you use join it will get create complete record will the all the fileds based on common fields

[louwriet]

Hi Chandra and Micheal,

Thank so much i have implemented your suggestion and it worked 100%. My problem is solved.

Regards

Louwrie