topic Chart doesnt show what it should in QlikView

Chart doesnt show what it should

Mon, 11 Jun 2012 11:08:29 GMT

Hello again,

I got:

One Excel file with input, out of it

one Chart with 3 Dimensions and 4 Expressions.

Dim: Buyer, Name, Group(Artikl IDs)

Expr:

- Item Only(ID February)

- Realized Turnover: sum(if(Year=2012, if(WildMatch(Group,[ID February])=1, LINEAMOUNT)))

- Planned Turnover: only([Planned Turnover February])

- %: Column(2)/Column(3)

My Problem:

Planned Turnover doesnt show up. I wrote by myself into the Excel table(for test purpose) the "Group" IDs and Planned Turnover but it still remains "-". Where could be the problem?

For some reason he cant connect the Plan to the Goup IDs but he recognizes the "ID February" in "only" Expression.

Thank you.

Kristian

Chart doesnt show what it should

swuehl — Mon, 11 Jun 2012 11:27:33 GMT

Could you upload a small sample file here?

I assume your issue is due to the fact that you are getting multiple, distinct Planned Turnover returned for your lines.

You could check this by using a sum() or concat() instead of the only() function.

Re: Chart doesnt show what it should

Mon, 11 Jun 2012 11:40:40 GMT

The input in the Excel File looks like this:

buyer - ID February - Planned Turnover

xx1 5 20000

xx1 6 50000

xx1 8 25000

Means, one Buyer is up to 10 times in the table the only cells that change are ID and Planned turnover

Re: Chart doesnt show what it should

swuehl — Mon, 11 Jun 2012 12:38:07 GMT

This is apparently not the only input table. How do your other tables look like?

How is this table connected to the table containing Group(Artikl IDs) (and is this a field? or a calculated dimension?

Re: Chart doesnt show what it should

Mon, 11 Jun 2012 12:52:55 GMT

No Calculated field. In the Attachment you can see that both tables are connected only to one table directly(Linije). The Group is table "Art" and the "iDent February" is table "Sheet2".

Group(grupa) contains contains numbers from 1-10, Ident February(Ident-veljača) calso contains numbers from 1-10, this should be the connection for planned turnover. The table recognizes the items that should be shown, but the planned turnover is "-".

The table should show: Only IDs(articles) that are mentioned in the Table Sheet2, with the sales sum and Planned turnover

Re: Chart doesnt show what it should

swuehl — Mon, 11 Jun 2012 12:59:49 GMT

>Group(grupa) contains contains numbers from 1-10, Ident February(Ident-veljača) calso contains numbers from 1-10, this should be the connection for planned turnover. The table recognizes the items that should be shown, but the planned turnover is "-".

Why do you think so? Group and Ident February are not linked directly, only via ITEMID and CUSTACCOUNT. I believe that might causing your problem.

Have you tried using a sum() or concat() to check if multiple, distinct values are returned?

Re: Chart doesnt show what it should

Tue, 12 Jun 2012 06:39:30 GMT

When i use the formula sum(Planned Turnover February) it shows me the table in attachment(table 1). With "only(" it shows (table 2), which is correct but with no Planned turnover.

Strange because the only values in Realized Turnover are 11 and 29(Group) and he sums the planned turnover on every row. It should be shown only groups 11 and 29, 20000 and 25000 as planned turnover.

Re: Chart doesnt show what it should

swuehl — Tue, 12 Jun 2012 08:06:55 GMT

I don't think that this is strange, you just don't have a valid link between group and ident February. Your tables are linked via CUSTACCOUNT and as you said, the Planned Turnover is not unambiguous for the buyer or customer.

Try maybe something like:

=sum(if(group=[ident February], [Planned Turnover]))

=only(if(group=[ident February], [Planned Turnover]))

Re: Chart doesnt show what it should

Tue, 12 Jun 2012 08:14:48 GMT

Thank you very much, this was the formula i was searching for.