Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

4 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.