Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Valued Contributor

Re: Sales Vs Target

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.

9 Replies
robert99
Valued Contributor II

Re: Sales Vs Target

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

Re: Sales Vs Target

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

Re: Sales Vs Target

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

Re: Sales Vs Target

Hi RJ

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

sebastiandperei
Valued Contributor

Re: Sales Vs Target

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
Contributor III

Re: Sales Vs Target

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

Re: Sales Vs Target

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

Re: Sales Vs Target

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
Contributor III

Re: Sales Vs Target

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