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

[whiteline]

Try to use " instead '.

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

[Anonymous]

Nick,

When I started reading, wanted to suggest the solution without set analysis - but later saw that you had it already.  This is the correct way in this case.  Set analysis does not evaluate data per dimension, hence it cannot be used in cases like this.

Regards,

Michael

[whiteline]

There is no need to "evaluate data per dimension". These set expressions uses search capabilities to reduce the set with only the values where [Supplier Code]=[Prime Supplier].

Then Sum function is used over this set in a chart with dimension.

Why it can't be used in a such way ?

[Anonymous]

In general, set analysis syntax [Supplier Code]=[Prime Supplier] is fine.  Or [Supplier Code]=P([Prime Supplier]).

In this case, where [Supplier Code] is a chart's dimension, you'll get the same result in each line because set "doesn't know" anything about charts and dimensions.  It evaluates overall data.

Regards,

Michael

[whiteline]

Just look at the attachment.

[Anonymous]

There is something here...  I'll take a look, thanks.

Followup: Tried.  I wish I was wrong...  The example with set analysis works only if both dimensions are in the same table.  Usually it is not the case, and it was - easy to resove in the script as well.  So, using "if" is the only solution.

[jagan]

Hi,

Hope attached file helps you.

Regards,

Jagan.

[#9]

Thanks Michael and Whiteline,

I have tried updating the expression to

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

and it's still showing 0.

I'm quite happy to use the Sum(If( expression if that's the correct way? I thought i had read that "Sum IF" had a higher overhead on the processor than set analysis.

Also, using SumIf doesn't seem to TOTAL correctly at the top? (Please see .bmp in original post for example).

I had originally used [Supplier Code]=P([Prime Supplier]) within the expression, however, unless a selection was made, the quantities were totalled across all suppliers. Please see code '000033' below;

This is with no selection made;

This is with the item code selected;

Note that the right column (Secondary Supplier) changes from 82 to 0 when the item code is selected...

[#10]

I'm assuming this is because p() analyses the selection, hence why i wanted to change the expression to not use P().

I also tried "Sum({$<[Supplier Code]=[Prime Supplier]>} [Orig. Ordered Qty])" but again, it evaluates to 0.