4 Replies Latest reply: Jan 25, 2014 8:33 PM by Ray HDKJSH RSS

    AGGR /GROUPING ISSUE

      Attached you will find this Straight Table. What I am trying to do is that I have Claim No and Occurence No. several Claims are tied together by the Occurence No. For Example Occurence NO  6773 has  several Claim No's.

      The Conditions to apply are


      1)Atleast one of the [CLAIM NO] within the SAME [OCCURRENCE NO] has a [CLAIM STATUS CODE] ='CLOSED'
        In the Attached OUTPUT [OCCURRENCE NO] 6773 has 5 [CLAIM NO]'S Linked to it and 3 of them have the
      [CLAIM STATUS CODE] ='CLOSED'.

      Once Condition (1) is satisfied then

      2))I need all the distinct Count  and Sum of Occurence No's where Sum(NET_INDEMNITY_PAID)  of all Claims        within the SAME OCCURENCE NO is   Less than 1000000 and greater than 0. For Example in the Attached File.
         [OCCURRENCE NO] 6773  SATISFIIES THE 1ST CONDITION WITH atleast one [CLAIM STATUS CODE] ='CLOSED' and has    a NET_INDEMNITY_PAID OF $50,000, SO I should get a count 1 for this Occurence and SUm of $50,000.


      I tried using the Range Sum Function Grouping by [Occurence No] as Follows but not the result I wanted.
      Rangesum(if(Aggr(Sum(NET_INDEMNITY_PAID),[Occurence No])>0 and Aggr(Sum(NET_INDEMNITY_PAID),[Occurence No])<1000000,Count(DISTINCT [Occurence No]),0),0,NoOfRows())

      Mr.Gysbert Wassenaar here on Community gave a good suggestion to use

      count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000'}>} distinct [Occurence No])

      It works partially. I am thinking that I NEED TO create a GROUP of OCCURRENCE NO AND  I might need to use the AGGR function but dont know exactly how to use it


      Its Complicated and I Hope I Explained it the Best I can. Any Help is appreciated

        • Re: AGGR /GROUPING ISSUE
          Ajay Krishnan Prabhakaran

          Hey Ray,

          Please check out the attached file.

          I created a table called Test which would be linked to your Excel table through Occurence number (key). Let me know if Test table meets your requirements.

           

          Thanks

          AJ

            • Re: Re: AGGR /GROUPING ISSUE

              Thnx for the Response, but there is one more Condition need to add  before we form the groups.

              IN the attached Excel Sheet Occurrence No 6197 have more than One Claims Closed , SO we need the Occurences with offcourse atleast 1 [Claim Status Code]='CLOSED' and atleast 1 CloseAdmin_Year=  the User Selected (Year). In the Attached Excel File, the user selected the Year='2013'In current situation for example if the User selects (Year) from the List Box Available. We should get the Occurences with atleast 1 User Selected Year as CloseAdmin_Year.

            • Re: AGGR /GROUPING ISSUE
              Srikanth P

              Hi Ray, First create the calculated Dimension like below:

               

                   IF( aggr(count({<[CLAIM STATUS CODE] ={'CLOSED'}>}CLAIM_CODE),  OCCURENCE_NO) > 0 , OCCURENCE_NO ) --> this cond give all the OCCURENCE_NO having atleast one Closed status code

               

              Then create your expression

                • Re: Re: AGGR /GROUPING ISSUE

                  Thnx for the Response,but I cant use the Calculated Dimension cuz I will use this number in a Value List Dimension. So It has to done in the Script like form the Group or do in the Expression.

                   

                  There is one more Condition need to add  before we form the groups.

                  IN the attached Excel Sheet Occurrence No 6197 have more than One Claims Closed , SO we need the Occurences with offcourse atleast 1 [Claim Status Code]='CLOSED' and atleast 1 CloseAdmin_Year=  the User Selected (Year). In the Attached Excel File, the user selected the Year='2013'In current situation for example if the User selects (Year) from the List Box Available. We should get the Occurences with atleast 1 User Selected Year as CloseAdmin_Year.