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

    Conditional dimension and expression

    Marcelo Gittermann

      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?

        • 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

          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!

          • Re: Conditional dimension and expression
            Stefan Wühl

            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!