Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sales Vs Target

Hi

I have been trying to create a dashboard for sales vs target, sales is pulled from live database and targets are uploaded using an excel sheet, the excel sheet has a set of values for eg

target.png

the above qty is the target value for each month, but in the actual sales being pulled from the database there is no sale ( actual qty) for the address AFRICA_3,

When i bring in both of these tables into qlikview AFRICA_3 appears as null and the actuals qty sums up all values for other address which are present in the database but need not appear here.

can somebody help resolve this issue.

1 Solution

Accepted Solutions
sebastiandperei
Specialist
Specialist

You are using a calculated Dimention, that only get the values of GCS_AddressLine3 when their Left(gcs_ORG,3) has the text 'SPP'.

If you make a table box, with gcs_ORG and GCS_AddressLine3 fields, you will see that AFRICA_3 has null in gcs_ORG. This is why AFRICA_3 don't appear in the Pivot Table. Fill this field of modify your calc. dimention if you want to see AFRICA_3.

If you don't want to see the other addresses (now they are gruped in the null, with AFRICA_3, because they don't have "SPP" in gcs_ORG), check Supress when value is Null in Dimentions, after select the address dimention.

View solution in original post

9 Replies
robert99
Specialist III
Specialist III

How have you loaded the target

Just asking. I plan to do something similar soon. I plan to concatenate the target to the live database table. But set up a new column as type (and label actual for the live database and target for the excel loaded target)

I will then  filter actual and target as appropriate.

Not applicable
Author

Hi,

Based on your information it is not so easy to answer your questions. I tried to visualise what could be wrong and what crossed my mind is that the link between the files might be not correct. It is typical that if data doesn't occur in both tables there is no match and for that reason the data is not showed. But as said you provide us with too little of information to answer your question. Maybe adding the qvw can be of help.

Regards,

Gerrit

Not applicable
Author

Hi Gerrit

I have attached the qvw and the excel sheet above, if you notice, in the address line there is a parameter called AFRICA_3 but in the qvw when i load, this does not appear as it has zero sales but 286 as the target, it appears just as a null symbol.

Not applicable
Author

Hi RJ

I have uploaded target using an excel sheet, pls find the example attache above

sebastiandperei
Specialist
Specialist

You are using a calculated Dimention, that only get the values of GCS_AddressLine3 when their Left(gcs_ORG,3) has the text 'SPP'.

If you make a table box, with gcs_ORG and GCS_AddressLine3 fields, you will see that AFRICA_3 has null in gcs_ORG. This is why AFRICA_3 don't appear in the Pivot Table. Fill this field of modify your calc. dimention if you want to see AFRICA_3.

If you don't want to see the other addresses (now they are gruped in the null, with AFRICA_3, because they don't have "SPP" in gcs_ORG), check Supress when value is Null in Dimentions, after select the address dimention.

manojkvrajan
Luminary
Luminary

Preethi, This is something you need to check the Data relationship. There is no GCS_ORG starting with SPP pertaining to AFRICA_3 and hence the application displays the value as null aggregating the details in Pivot table. Attached is the locked version confirming the fact. Does it help?

Not applicable
Author

Hi Manoj and Sebastian , both your inputs helped, i added a new column in the excel as gc_org for spp and the AFRICA_3 appears, thank you

Not applicable
Author

Hi

There is an issue now, i have added a column with Gc_org and it shows AFRICA_3, but if i make a month selection it again appears as Null, and i cannot add a month column in the excel sheet as it doesnt match with the data from the database.

manojkvrajan
Luminary
Luminary

Preethi, Please double check the data considering no GCs_Invoice_Dt association to the AFRICA_3. It displays correct with the available data.  Screenshot below for your reference. Preethi.PNG