3 Replies Latest reply: Jan 5, 2012 5:48 AM by Roland Kunle RSS

    Set Analysis Not Working

      Dear All,

       

      I am calculating rate using a complex Set Analysis in straight table.

      It is working but I notice that at the end of table there is no total for this column.

       

      Yes, I find out the region of this, that is that "I am not using any aggregate function before this complex set analysis".

      As soon as I am using sum or avg before this I am getting null error.

       

      Here I am help less, I don't know how to solve it.

       

      Please help.

       

      I am attaching my sample QVW here

       

      Thanks & Regards

       

      - anil

        • Re: Set Analysis Not Working

          Hello anil,

           

          I analyzed your expressions and I found (at least) two reasons, why you have dashes (undefined values) in your expressions. And as a conseqence in the according totals.

          1.

          sometimes you are dividing by zero --> expression result is undefined, this is a '-'

          Example see third expression in my copied chart --> your divisor is 0

          2.

          In some cases your ifs are both "false" and returning a null-value

          Example see forth expression in my chart. For testing purpose I used a '-1' as default value

           

          HtH

          Roland

           

          Note:

          I didn't miss any aggr()-functions like you said in your post. Look at the reasons I mentioned above.

            • Re: Set Analysis Not Working

              Roland Sir,

               

              Very good morning, now it is morning in India, Thanks for your reply.

               

              I have Solved this problem, by changing my Set Analysis writing approach. Now I am writing This

               

              (

                        (

                                  SUM( {$ < trnType={'Sales'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } (BasicAmt*iExchangeRate) ) -

                                  SUM( {$ < trnType={'Sales'}, fabType={'Synthetic'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } (stdPackAmt-PackAmt) )  

                        ) -

                        SUM( {$ < trnType={'CRN','SALRET','PROCRN'}, crnType={'SP'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } invGrTotal )

              ) / sum( {$ < trnType={'Sales'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } Sqmtr )

               

              But I will always consider all the points you suggested here in your mail.

               

              Thanks a lot

               

              Regards.

               

              -anil