topic Re: sales vs targets - giving wrong target values in QlikView

sales vs targets - giving wrong target values

louwriet — Tue, 08 Jan 2013 03:21:52 GMT

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

Re: sales vs targets - giving wrong target values

michael_anthony — Tue, 08 Jan 2013 03:52:06 GMT

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.

Re: sales vs targets - giving wrong target values

Tue, 08 Jan 2013 04:25:38 GMT

Hi,

Please see the attached file..

Hope it will helps!!

Chandra

Re: sales vs targets - giving wrong target values

michael_anthony — Tue, 08 Jan 2013 05:07:03 GMT

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.

Re: sales vs targets - giving wrong target values

Tue, 08 Jan 2013 06:06:17 GMT

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

Re: sales vs targets - giving wrong target values

louwriet — Fri, 11 Jan 2013 12:18:37 GMT

Hi Chandra and Micheal,

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

Regards

Louwrie