Conditional dimension and expression

[marcelo_7]

Hello,

I have orders placed to suppliers and are destined to STOREs. Each order recieves an ORDER_NO. Orders can either be placed by empoyees with different USERID or automatically. Orders that are automatically placed by the computer have the USERID COMP for simplicity.

Now, I have managed to create a straight table but I would like to reduce it and I'm asking for your help.

The straight table has the dimensions SUPPLIER and STORE

I have two expressions:

Show orders placed by the computer

count({$<USERID={'COMP'}}>}DISTINCT ORDER_NO)

Show orders placed by employees

count({$<USERID-={'COMP'}}>}DISTINCT ORDER_NO)

There are some connections between suppliers and stores where the computer hasn't placed any orders and where employees have placed orders.

I would like to limit the straight table to show only the suppliers and stores where there is at least one order placed by the computer.

I tried changing making a calculated dimension out of STORE but without success. I also tried to remove the dimension STORE and make it as a condition but it didn't work either. Any thoughts?

[marcelo_7 · Accepted solution]

Playing around with it and searching the forums a second time I managed to find a working solution.

The fist expression with label "Orders placed by COMP":

if(count({$<USERID={'COMP'}}>}DISTINCT ORDER_NO)>1, count({$<USERID={'COMP'}}>}DISTINCT ORDER_NO), null())

Second expression:

if(Column(1)<>null(), count({$<USERID-={'COMP'}}>}DISTINCT ORDER_NO), null())

My problem was that my second condition before was

if(Column(1)=null()....

but it seems it doesn't compare it properly so this is a workaround. Another improvement would be to avoid the repetition of the first expression.

Hope this helps anyone!

[swuehl]

I think you just use a conditional on your show orders placed by employees:

=if(

count({$<USERID={'COMP'}}>}DISTINCT ORDER_NO),

count({$<USERID-={'COMP'}}>}DISTINCT ORDER_NO)

)

If suppress zero values option is checked in presentation tab, this should remove the lines where both expressio result in zero.

edit:

Just have seen that you found the solution already. Have fun!