topic Re: Sales Vs Targets in QlikView

Sales Vs Targets

cpiocpio — Fri, 11 Jul 2014 11:04:38 GMT

The attached file illustrates the issue with the Target Method deployed here.

Year 2010, has Regions 0 to 6, but because the Region 6, which has a target of £250,000 for the year

has yet to make a sale, it is excluded from display of sales for that year

because of the join between Target and Sales.

The full target for 2010 is £141,950,004 but the values displayed is £250,000 less than it should be because Region 6 is excluded.

Re: Sales Vs Targets

Fri, 11 Jul 2014 11:46:51 GMT

Hi, Mike.

I think the problem is that you don't have any operations with customer Moon Depot in your Sales Data. This is the only one customer from region 6.

You have data for this customer in the other tables, but the information in the chart is coming from Sales Data and there isn't information for operation from this customer.

You can see why don't have any operations with this customer and repair this error.

You can see and the attached picture, because there are information about InformationDensity of the field.

Regards,

Venelin

Re: Sales Vs Targets

cpiocpio — Fri, 11 Jul 2014 12:04:57 GMT

Hi Venelin

Thanks for the reply.

I know there is NO data for Region 6, that is the point but there is a TARGET.

So what I need is to show that Region 6 has £0 Sales but a Target of £250,000

Note my point that the Full Yr Target Value is showing short by £250K

Mike

Re: Sales Vs Targets

datanibbler — Fri, 11 Jul 2014 12:06:35 GMT

Hi,

that sounds very probable - I cannot see it, downloading those 5MB would take roughly 30min 😉

You can check that in the script with the NoOfRows() function and, in case there are no records, just construct a dummy record and attach that so you won't have the problem of any data_points missing.

Re: Sales Vs Targets

jonathandienst — Fri, 11 Jul 2014 12:20:12 GMT

This is the sort of problem caused by having multiple fact tables. I would see whether it is possible to concatenate the forecast facts to the sales facts in a single table, together with a 'Type' field that differentiates the data.Put the Forecast amount into a different measure field too, so that your existing Sales metrics are not affected.

This approach avoids the problem of incomplete joins cutting the data - Region 6 in your case. By concatenating Region 6 will be included in the forecast. Don't worry if there are lot of fields that do not exist in the forecast table. They will simply be null for the forecast lines in the combined fact table

HTH

Jonathan

Re: Sales Vs Targets

Fri, 11 Jul 2014 12:34:06 GMT

Ok,

Add new ListBox in the layout with Region Year's field, then select Region Year 6_2010.

In this case, you can see that there are row with values, but with missing reigon and year.

You have some ways to update this value for region and year, in this.

You can write IF statment in the scriprt, something like that:

IF region = null then

region = first string from Region Year

because in the Region_ Year's filed there are values and has to be work properly;

Regards,

Venelin

Re: Sales Vs Targets

cpiocpio — Fri, 11 Jul 2014 12:56:28 GMT

Hi All

I Think I have sorted,it was the comment by DataNibller then sent me down a road of

" just construct a dummy record"

So I have done exactly that, I did inline load of a customer record in Region 6 (The Moon), with a zero value, but invoice date in the applicable year.

See attached