Chart doesnt show what it should

[#1]

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

[swuehl]

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.

[#3]

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

[swuehl]

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?

[#5]

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

[swuehl]

>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?

[#7]

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.

[swuehl · Accepted solution]

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]))

or

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

[#9]

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