Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
marcelo_7
Creator
Creator

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)

9 Replies
whiteline
Master II
Master II

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

It seems it works.

Just toggle 'Supress when value is null' checkbox.

marcelo_7
Creator
Creator
Author

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

marcelo_7
Creator
Creator
Author

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.

whiteline
Master II
Master II

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)

marcelo_7
Creator
Creator
Author

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.

whiteline
Master II
Master II

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

marcelo_7
Creator
Creator
Author

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.

whiteline
Master II
Master II

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)

marcelo_7
Creator
Creator
Author

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!