5 Replies Latest reply: Oct 17, 2013 7:24 AM by Nayan Lalla RSS

    Combining  expressions on the front end

    Nayan Lalla

      Hi

       

      I need help with combining  expressions.

       

      Inividual Formulae :

       

      Stock on Hand =  sum( if(  [Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand])

       

      Stock on Purchase Order =  sum([ Qty on PO] )

       

      Stock on Sales Order = sum ([Qty on SO])

       

       

       

      The formula i want to create is the following:

       

      Stock Required :

       

      If (Stock on hand - Stock on Purchase Order - Stock on Sales Order  < 0 ,0 , Stock on hand - Stock on Purchase Order - Stock on Sales Order.

       

      The formula works when i use the headings , however i want to create the formula using the actual expressions in each of the individual expressions.  How do i do this.

       

      Your help would be appreciated.

       

      kind regards

      Nayan

       

       


        • Re: Combining  expressions on the front end
          Jonathan Dienst

          Hi

           

          I would use RangeMax rather than the <0 condition in your final expression. Like this:

           

          RangeMax(0,

            sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand])

            - Sum([ Qty on PO])

            - Sum([Qty on SO]))

          )

           

          The RangeMax will ensure that -ve values return 0.

           

          HTH

          Jonathan

            • Re: Combining  expressions on the front end
              Jonathan Dienst

              Oops, a misplaced parenthesis:

               

              RangeMax(0,

                sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand]))

                - Sum([ Qty on PO])

                - Sum([Qty on SO])

              )

                • Re: Combining  expressions on the front end
                  Nayan Lalla

                  Hi Jonathan

                   

                  Thank you for your repsonse.  Your formula works for a chart straight table (by this i mean the total is correct).  However , with the pivot table, the total is incorrect.  Below is a result which i export to excel.

                   

                  How do you make the formula work for a pivot table .

                   

                  I cannot upload my QV model as it is too big and also sensitive information.  What i may be able to do create a sample of the model.

                   

                  kind regards

                  Nayan

                   

                  Pivot Table
                  Component CodeITEM  CODEJonathan Test
                  FEEMSFBCP1219189
                  FEEMSFBCP141348
                  FEEMSFBCP150
                  FEEMSFBCP15L0
                  FEEMSFBCP168876
                  FEEMSFBCP250
                  FEEMSFBCP25L0
                  FEEMSFBCP290
                  FEEMSFBCP316793
                  FEEMSFBCPGL40
                  FEEMSFBCPGL50
                  FEEMSFBCPMP10
                  FEEMSFBCPMP2150
                  FEEMSFBGLEMS0
                  FEEMSFEEMS0
                  FEEMSFEEMSGL0
                  TOTAL

                  0

                  Straight Table
                  Component CodeITEM  CODEJohnathan
                  FEEMSFBCP1219189
                  FEEMSFBCP141348
                  FEEMSFBCP150
                  FEEMSFBCP15L0
                  FEEMSFBCP168876
                  FEEMSFBCP250
                  FEEMSFBCP25L0
                  FEEMSFBCP290
                  FEEMSFBCP316793
                  FEEMSFBCPGL40
                  FEEMSFBCPGL50
                  FEEMSFBCPMP10
                  FEEMSFBCPMP2150
                  FEEMSFBGLEMS0
                  FEEMSFEEMS0
                  FEEMSFEEMSGL0
                  TOTAL 36356