2 Replies Latest reply: Oct 9, 2012 6:15 AM by Stefan Wühl RSS

    Conditional dimension and expression

    Marcelo Gittermann



      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?

        • Re: Conditional dimension and expression
          Marcelo Gittermann

          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



          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!

          • Re: Conditional dimension and expression
            Stefan Wühl

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



            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.



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