Set Analysis where Field A = Field B query

[#1]

Hi Guys,

I'm trying to create a straight table using Set analsis where it sums the quantity based on whether the Supplier Code field is equal to the Prime Supplier Field, the Secondary Supplier Field or Tertiary Supplier Field.

To do this i've been using the below Expression;

Expression Title                         Expression

Prime Supplier Qty =                     Sum({$<[Supplier Code]={'=[Supplier Code]=[Prime Supplier]'}>} [Orig. Ordered Qty])

Secondary Supplier Qty =              Sum({$<[Supplier Code]={'=[Supplier Code]=[Secondary Supplier]'}>} [Orig. Ordered Qty])

Tertiary Supplier Qty =                   Sum({$<[Supplier Code]={'=[Supplier Code]=[Tertiary Supplier]'}>} [Orig. Ordered Qty])

However, it doesn't seem to be working correctly;

If also written the expression without set analysis (See right column) and it seems to work so can someone tell me what i'm doing wrong?

Regards,

Nick

[#2]

I've been trying to figure out how to do this but i'm clearly missing a field within the expression. I need to include the field [Item Code] in expression somewhere so that it can identify the correct Primary and Secondary Supplier.

What i'm trying to achieve overall is the below;

So that we can identify the quantities and spend by Item from each supplier.

I'm still using the set analysis expression;

sum({$<[Supplier Code]={"=[Supplier Code]=<[Item Code]>[Prime Supplier]"}>}[Orig. Ordered Qty])

as this works when a single line is selected. However, when no selections are made, the table shows all values as 0 for those set analysis (as shown above).

If i need it to evaluate the Item Code as well, how do i incorporate this into the expression?

Regards,

Nick

[whiteline]

It's strange. In a simple example everything works fine. I think there is some problems with nulls and table connections or combination of dimensions in this chart.

You could also try text comparison in sets:

Sum({$<[Supplier Code]={"=text([Supplier Code])=text([Prime Supplier])"}>} [Orig. Ordered Qty])

With "ifs" you can try something like this (it gives correct totals):

=Sum(aggr(if([Supplier Code]=[Prime Supplier], Sum([Orig. Ordered Qty]), null()), [Item Code]))

I' use [Item Code] as the lowest level demension that represents row of table. You could have to correct that.

[#4]

Thanks Whiteline,

I've attached the file so you can see what i mean.

With no selections made, the Qty columns for Prime, Secondary and Tertiary are shown as blank (not always though, as is the case with 000301).

Once you select an item code, those columns populate with the correct figures.

I can only assume that this is because when no selections are made, the Set Analysis cannot determine the item code and therefore cannot determine who the Prime, Secondary and Tertiary Suppliers are.

Does anyone have any ideas on this?

Regards,

Nick

[whiteline]

I think, I've found it.

[Supplier Code] and [Prime Supplier] are fields from different tables connected by Item Code.

As you can see on the attached screen (tablebox) the relations between them are ambigous

(there are many paths through Item Code except 000301 and supplier code 101883).

So I think such logic of sets unfortunately is not applicable in this case.

[Anonymous]

Agree, set analysis is not applicable here.  Looks like it may work in situation when all fields in set are in the same table.

Regards,

Michael

[whiteline]

I think your problem could be solved if there was a way to overcome the ambiguity.

May be, some id field that distinguish one shipmentfact from another could help.

You can then use aggr() explicitly.

But may be, it's time for refactoring the data model...

[#8]

Could someone explain why they need to be in the same field for set analysis to work as the relationship between the tables is one to many? I don't doubt that you're right, i just don't undertsand why.

To explain further; each item code can only have one Prime Supplier, one Secondary Supplier and one Tertiary Supplier. However, each item code may have been purchased from any supplier code.

If the relationship is one to many, regardless of who the items were purchased through, any item code will alway have the same Prime, Secondary and Tertiary Supplier. Doesn't this solve the ambiguity or am i missing something?

Thanks for your help with this, I hope this doesn't come across as ungrateful as that is not my intention. I just want to understand why this isn't working.

Regards,

Nick

[#9]

Could someone explain why they need to be in the same field for set analysis to work as the relationship between the tables is one to many? I don't doubt that you're right, i just don't undertsand why.

To explain further; each item code can only have one Prime Supplier, one Secondary Supplier and one Tertiary Supplier. However, each item code may have been purchased from any supplier code.

If the relationship is one to many, regardless of who the items were purchased through, any item code will alway have the same Prime, Secondary and Tertiary Supplier. Doesn't this solve the ambiguity or am i missing something?

Thanks for your help with this, I hope this doesn't come across as ungrateful as that is not my intention. I just want to understand why this isn't working.

Regards,

Nick

[#10]

I've joined the tables into one and have checked that there is nothing abnormal about the join. Each Item Code only has one Prime, Secondary and Tertiary Supplier.

It's still not working though...?

Please find enclosed latest file.

[whiteline]

The question in not about joining, i think.

Could you add some id field (for example rowno()) as I've mentioned before ?

Becaue the Qty expression works if I use something like this:

sum({$<[Due Date]={"=[Supplier Code]=[Prime Supplier]"}>}[Orig. Ordered Qty])

With [Supplier Code]={"=[Supplier Code]=[Prime Supplier]"} you just CANN'T select all records that have [Supplier Code]=[Prime Supplier] because there both situations:

[Supplier Code]=[Prime Supplier] and [Supplier Code]<>[Prime Supplier] with the same [Supplier Code].

[Due Date] in your simplified case distinguishes these situations.