topic Re: Adding a quota spread sheet to a load in QlikView

Adding a quota spread sheet to a load

Mon, 02 Apr 2012 15:07:43 GMT

Hi,

I have a report that loads information from 2 QVD files.

One of them has the Employee information such as Name, Phone Nr

and the second one hold calls information such as Phone Nr, date, month and nr called.

Now When I try to add a quota for every employee it doesn't work..

i have added a spreadsheet that hold Name, Phone nr, Month, and month Quota..

the call quota doesn't work..it is always showing zeros..

Anybody can help me please?

Thxs

Re: Adding a quota spread sheet to a load

jonathandienst — Mon, 02 Apr 2012 15:40:32 GMT

This question is a little too general. I think you will need to provide more information and a sample model and data files if possible so that someone can assist you.

Regards

Jonathan

Re: Adding a quota spread sheet to a load

Mon, 02 Apr 2012 15:47:42 GMT

Hey Jonathan,

Attached is a data sample and the Excel file as well..

On the third tab in the document there us a quota chart where I am having the Quota as zeros.

on this chart I am trying to get two expressions Total calls, total quota so i can compare them.

Re: Adding a quota spread sheet to a load

Mon, 02 Apr 2012 15:56:41 GMT

here's an example with sample data

Re: Adding a quota spread sheet to a load

Mon, 02 Apr 2012 16:00:55 GMT

Hi juan,

I dont have the issue on loading the data but on using it.

I already loaded it but when I use it on the chart i get No data to display..

Thxs

Re: Adding a quota spread sheet to a load

Mon, 02 Apr 2012 16:10:11 GMT

In other words when I added selected fields to the document to check what's going on..

I have added the Name, Phone nr and MonthQuota..

now when I click on the Phone Nr or on the Name the MonthQuota doesn't get effected ..

Like it is not Joined!

Thxs and hope you will be able to help me.

Re: Adding a quota spread sheet to a load

jonathandienst — Mon, 02 Apr 2012 16:11:33 GMT

There appears to be a problem with your month field. Some of the values (without quotas) are numeric month values (dual, so the display the month name, like 'Jan', but actually a numeric under the skin), and text month names (with quotas - from the spreadsheet).

You need to convert the spreadsheet month names to numeric month values. The easiest way to do this is to change the month field in he spreadsheet to contain a date value (say the first of the month), and use

LOAD EMPLOYEENUMBER,

Month(Month) As Month,

MonthQuota

in your load script.

Hope that helps

Jonathan

Re: Adding a quota spread sheet to a load

Mon, 02 Apr 2012 16:39:45 GMT

Thxs..

Over all I did change the values on the spreadsheet to nrs 1 to 12 for every employee and that seems to work fine.

the only thing i need now is to exculd people with quota of zero..

I check surpass zero values but that didn't take them out.

Any idea?