topic Re: chart problem in QlikView

chart problem

arulsettu — Sat, 08 Mar 2014 06:23:54 GMT

hi i created a chart. and i added this code to

=class(floor((Today()-BirthDate)/365.25),$(agerange),'Age')

calculated dimension.

expression is sum(sales).

i created a slider and added a variable agerange.

but the result is nothing like this

i need to display like this

Re: chart problem

arulsettu — Sat, 08 Mar 2014 06:45:02 GMT

here i attached my app in customers tab check the chart

any one can help? thanks...

Re: chart problem

maxgro — Sat, 08 Mar 2014 07:22:51 GMT

in your database you have

the table HR_Employee with ContactID and BirthDate

the table OrderHeader with ContactID and TotalDue

but the ContactID with TotalDue hasn't the BirthDate (they are missing in HR_Employee)

and the ContactID with BirthDate doesn't have TotalDue (they are missing in OrderHeader)

first put in place your db

second it seems to me you're using the variable agerange in calculated dimension but the variable is missing

Re: chart problem

arulsettu — Sat, 08 Mar 2014 07:31:24 GMT

hi Massimo

i have created variable agerange. hoe can i display the data any idea ? thanks...

Re: chart problem

MK_QSL — Sat, 08 Mar 2014 07:42:39 GMT

Dear Arul,

I have created a straight table for BirthDate and SUM(TotalOverDue)... There is no relation between both of them.

BirthDate	SUM(TotalDue)
	$140707584.82
	$140707584.82

Re: chart problem

maxgro — Sat, 08 Mar 2014 07:45:43 GMT

something is wrong in the data of your db

the ContactID of OrderHeader should be also present in HumanResource_Employee table

for now they are missing

I can't fix your source data (access db?)

Are you sure in access db you use as sourcedata,

the ContactID of HumanResource_Employee

are the ContactID of OrderHeader?

I don't think. Try to check this first in Access.

Re: chart problem

maxgro — Sat, 08 Mar 2014 07:47:45 GMT

yes probably the ContactID are different in access db (the source)

Re: chart problem

arulsettu — Sat, 08 Mar 2014 07:54:14 GMT

contact id in HumanResource_Employee table

and contact id sales_orderheader table are linked. and its all in same database... i can see the relation in table viwer

Re: chart problem

arulsettu — Sat, 08 Mar 2014 07:58:19 GMT

i think contact id in both tables are linked... am i correct?

Re: chart problem

maxgro — Sat, 08 Mar 2014 08:01:19 GMT

the are linked (correct)

but they always have different ContactID (problem; from the access db I presume)

if you create a chart like this and two listbox BirthDate and SalesOrderID you can find the problem

ContactID with TotalDue and without BirthDate

ContactID with BirthDate and without TotalDue

Re: chart problem

arulsettu — Sat, 08 Mar 2014 08:05:03 GMT

ya i noticed. different values in contact id. i think ur right. now display both values in the tables what should i do?

Re: chart problem

MK_QSL — Sat, 08 Mar 2014 08:09:16 GMT

I found problem now...

If you check your HumanResource_Employee table... all ContactID starting with 4 digit

while in your orderheader table, all ContactID who are having at least something as TotalDue are not a single from HumanResource table.

You can check the same by creating a table box

of HumanResource_Employee and

orderheader

Check enclosed file....

Re: chart problem

arulsettu — Sat, 08 Mar 2014 08:12:58 GMT

hi.

thanks for ur reply. i noticed that. now display both values from two tables how can i do that?

Re: chart problem

MK_QSL — Sat, 08 Mar 2014 08:16:53 GMT

I have already shown in the enclosed file in my previous reply.

New Sheet Object

Table Box

Select Table from Show Fields from Table

Add All (Dimensions)

Do the same for second table.

Now Select any ID from 1st Table, You will get no value in second table..

Hope you now get it what I am trying to tell you..

Re: chart problem

maxgro — Sat, 08 Mar 2014 08:17:21 GMT

your chart is correct when you add the variable and adjust the source data

I tried with a binary load of your qvw and then I rebuild the HumanResources_Employee table

See attachment

Re: chart problem

arulsettu — Sat, 08 Mar 2014 08:20:05 GMT

sorry i cant able to open your file i am using personal edition. can u paste the code please...

thanks

Re: chart problem

arulsettu — Sat, 08 Mar 2014 08:20:46 GMT

hi Manish

sorry using personal edition...

Re: chart problem

maxgro — Sat, 08 Mar 2014 08:28:47 GMT

copy your qvw in the same folder (Sale management 1.qvw)

modify Sales management 1.qvw

- binary load your sales management.qvw

- drop HumanResources_Employee

- rebuild HumanResources_Employee with a random birth date using ContactID from OrderHeader

this is the SCRIPT in Sale management 1.qvw

Binary [sale management.qvw];

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

DROP Table HumanResources_Employee;

HumanResources_Employee:

LOAD

ContactID,

floor(Today() -rand()*20000) as BirthDate;

;

Load distinct

ContactID

//BirthDate

Resident OrderHeader;

exit script;

Re: chart problem

arulsettu — Sat, 08 Mar 2014 08:38:42 GMT

thanks everyone.....

Re: chart problem

MK_QSL — Sat, 08 Mar 2014 08:41:55 GMT

To sort the chart in proper order use below...

Change the dimension as below..

=DUAL(class(Floor((Today()-BirthDate)/365.25),$(agerange),'Age'),class(Floor((Today()-BirthDate)/365.25),$(agerange),'Age'))

Sort Tab

Numeric Value ==> Ascending order