Target vs Actual not showing zero if there was no sales for product

[louwriet]

Hi,

I have this issue that i need to solve urgently. I have sales vs target in a table. When you select a sales month - the sales and targets are displayed in two columns next to each other - each one has it's own expression  sales column expression = sum(VALUE)

Target column expression is :

= sum(IF (VMaxMonth = 01 , Target Jan 2013

             IF (VMaxMonth = 02 ,Target Feb 2013

                IF ( VMaxMonth = 03, Target  March 2013)

                 )

              )

          )

VMaxMonth is a variable with the max month in and in the expresion  it works fine , and the correct target is pulled through if there were sales and the product has a target

My problem is that if i have a target against a product that were not sold that month, the product is not shown in the table and i need it to display product 0 sales and the target next to it.

And also if there was a sale for the product and this product does not have a target - the sales must be displayed and in the target column there must be a 0 displayed.

See attached sheet 2 one of how diagram look like and workings 1 of result set of the table and how it should

Thank so much

Regards

Louw

[#2]

Hi Louw,

can you post a simple qvw with your table?

regards

Giampiero

[#3]

Have you tried looking at the Dimension tab and making sure the Show All Values is checked?

[louwriet]

Hi Giampiero,

I have created a demo model to explain better - see attached also included is the excel datafiles.

My problem is that PRODUCT1 must also show next to SHOP3 and SHOP4 with 0 Month sales because there were no sales for them for FEB but they have a target next it so it must show 0 sales and the targeted value. It must look like SHOP2 with a 0 in sales for product one.

Hope this explains it better. See attached files. Could not load the div team file(max 5) - but its a one liner with Division CHRONIC and team MEDICAL

Thank so much

Louw

[louwriet]

Hi ladyesther,

Thanks for reply, have tried your suggestion but it didnt work - see my new post on issue.

Thank so much

Louw

[#6]

Hi,

Did you find a solution ?

[#7]

Hi Louwire,

did you find a solution for your issue?

Regards,

[Vidit]

Hi,

create two table e.g.

PROD_DESC:
load distinct
PRODUCTCODE,
     [PRODUCT DESCRIPTION] AS PROD_DESC
     FROM
C:\PATH\TRANSACTIONFILE.xls
(biff, embedded labels, table is Sheet1$);

PROD_NAME:
load distinct
PRODUCTCODE,
     Name AS NME
     FROM
C:\PATH\TRANSACTIONFILE.xls
(biff, embedded labels, table is Sheet1$);

Use PROD_DESC and NME columns instead of [Product description] and NAME

[#9]

Hi Louwrie, instead of using for sales =sum(Value), use numsum(Value), this way if there were no sales that month it will return null

[Roop]

I think that the easiest method (from a QlikView perspective) will be to ensure that the file includes all sales. In other words it is relevant that there were zero sales with that combination and that is crucial to your application.

QlikView is removing the combination as there are no records that fulfill that particular criterion. So the input file should include all sales (even if zero) and this will then function according to your requirements.

These are the 2 rows I added:

01/02/2012	880610_2484	2484	SHOP3	880610	PRODUCT1	0
01/02/2012	880610_2644	2644	SHOP4	880610	PRODUCT1	0

Hope this helps