Set analysis - with 2 fields

[dafnis14]

Hi,

I have a chart with a dimension SupplierCode.

i would like to use the dimension in order to select values linked to the current supplier

in another table.

The following does not work:

sum({SupplierCode = P(Suppl.SuppCode>}Suppl.Quantity*Suppl.UnitPrice)

would appreciate help with this issue!

Thanks!

[jvitantonio]

Try this:

sum({< SupplierCode = P(Suppl.SuppCode) >} Suppl.Quantity*Suppl.UnitPrice)

Thanks

Jv

[PradeepReddy]

try these

sum({<SupplierCode ={'*'}>}Suppl.Quantity*Suppl.UnitPrice)

[#4]

I don't really understand what you are trying to do. becaseu as soon as you select a supplier code the other table should show the right values.

regards,

MT

[dafnis14]

Thanks.. but it didn't help.. 😞

[dafnis14]

Hi,

There is no link between the tables.

Let's assume you have 2 tables with suppliers facts with no common supplier code key:

Suppliers and Suppl.  The supplier code has different field name in the tables.

The SupplierCode from Suppliers table is a dimension in a chart.

Not all the suppliers that are in the chart have values in Suppl table.

But for the ones that do have, I want to be able to show the following  :

sum({SupplierCode = P(Suppl.SuppCode)>}Suppl.Quantity*Suppl.UnitPrice)

Thanks!

[PradeepReddy]

Hi,

Can you please provide some sample Application or Data... So that it will be easy to analyse the scenario.

Thanks,

Pradeep

[jvitantonio]

Hello Dafnis,

You are missing "<" after your curly bracket.

It should be like the following:

sum({<SupplierCode = P(Suppl.SuppCode)>}Suppl.Quantity*Suppl.UnitPrice)

Thanks,

JV

[jonathandienst · Accepted solution]

Hi

First of all, you cannot use a dimension value in a set expression. The set expression is evaluated once for the chart and not once per dimension value.

Secondly, you need to get information from the Suppl table. A selection of Supplier does not help as this is not linked to Suppl. You need something like this:

     Sum(If(Suppl.SuppCode = SupplierCode, Suppl.Quantity * Suppl.UnitPrice))

If that performs poorly (sum(if()) is a performance killer especially across two tables), you need to look more closely at your data model. The most common design is a star schema, where the supplier dimension should be linked to the fact table by the supplier code. You can do this when loading the suppliers by aliasing the Suppl.SuppCode to SupplierCode. Something like:

          LOAD

               ....

               Suppl.SuppCode,                                        // use this field to see which suppliers exist in the dimension

               Suppl.SuppCode As SuplierCode,          // this field links to the fact table

               ...

          FROM Suppliers....

Then in a table that has SupplierCode as a dimension, just use

     Sum(Suppl.Quantity * Suppl.UnitPrice)

Perhaps you could post a reduced data sample of your qvw here.

HTH

Jonathan

[dafnis14]

Hi Jonathan,

That's what I've done.

Thanks for this helpful advise!