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.


          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


          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






          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