topic Re: Re: AGGR /GROUPING ISSUE in QlikView

AGGR /GROUPING ISSUE

Fri, 24 Jan 2014 22:18:52 GMT

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

Fri, 24 Jan 2014 22:44:59 GMT

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

Re: AGGR /GROUPING ISSUE

Fri, 24 Jan 2014 23:28:06 GMT

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

Sun, 26 Jan 2014 01:01:29 GMT

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: Re: AGGR /GROUPING ISSUE

Sun, 26 Jan 2014 01:33:53 GMT

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.