9 Replies Latest reply: Sep 19, 2012 6:27 AM by Marcelo Gittermann

Calculated dimension if condition

Hello,

I have a table containing a list of partnumbers, supplier of that part number, to what store and the vendor partno which is different from ours.

In this case two suppliers deliver three different partno but we sell it as the same. As you can see in the example below one supplier has two part numbers for our part no and we want to catch those errors.

Partno     Store     Supplier     Sup.Partno

1000             1               A              37

1000             2               A              37

1000             1               A              38

1000             1               B              76

i have a pivot table

with the dimensions

Partno

Supplier

Sup.Partno

and the expression

count(Sup.Partno) because i want to see how many occurences there are (=how many stores have that partno)

In my pivot table i want to show only suppliers that have two partno for one of ours and not show the others.

By checking out the community I thought that my dimension expression would work but it didn't and I would like some help.

Dimension Supplier:

aggr(if(count(distinct Sup.Partno)>1, Supplier, null()), Supplier)

• Re: Calculated dimension if condition

I thought that my dimension expression would work but it didn't

It seems it works.

Just toggle 'Supress when value is null' checkbox.

• Re: Calculated dimension if condition

I must have missed to include another field that messes it up. Because when I do this example myself it works.

• Re: Calculated dimension if condition

I have attached a qvw for you to try. In the right one "A with aggr()" all are visible. But if you click 0101 it shows nothing because the expression doesn't apply after the selection. But according to me it doesn't apply before but it still shows all Partno. I'm missing something. I also included the an extended version of the inline example for you convenience.

Thanks in advance.

• Re: Calculated dimension if condition

As you want select vendors having more than one vendorPartNo in general (not in current selection), correct your set analysis expression:

=aggr(if(count({1<Active={1}>} distinct vendorPartNo)>1, vendor, null()), vendor)

• Re: Calculated dimension if condition

I tried adding the 1 and also tried with \$1 but neither of them work properly. All are shown and when clicking either partno the partno is shown even though only 1005 should be shown.

• Re: Calculated dimension if condition

I think I've missed something... why only 1005 ?

• Re: Calculated dimension if condition

Because 1005 has two vendor part numbers for one of ours. And we don't want doubles. It would be problematic to if we bought two different products from a vendor and sold them as the same. Therefore I want to catch the suppliers who have several vendor_part_no for one of our partno.

• Re: Calculated dimension if condition

In this case I guess you use the wrong logic.

As I understand you want to check vendorPartNo count for every pair vendor-partNo

I think this should work:

=aggr(if(Max(aggr(count({1<Active={1}>}distinct vendorPartNo), vendor, partNo))>1, vendor, null()), vendor)

• Re: Calculated dimension if condition

It works for the example qvw but when I apply it to the original data partno that don't apply show up as results. I'll have to dig deeper. Thanks for taking the time helping me though!