topic Re: Rangesum Issue in QlikView

Rangesum Issue

Thu, 23 Jan 2014 18:13:49 GMT

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

I need all the distinct Count of Occurence No's where Sum(NET_INDEMNITY_PAID) of all Same Occurence Nos is Less than 1000000 and greater than 0.

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())

Any help is appreciated

Re: Rangesum Issue

Gysbert_Wassenaar — Thu, 23 Jan 2014 18:19:43 GMT

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

See attached qvw for an example.

Re: Re: Rangesum Issue

Thu, 23 Jan 2014 18:38:47 GMT

Thnx for the quick response, Its better than what I had, I am still off ,if you look at the attached table using ur Expression its still counting Each Occurence No instead of doing Distinct

Re: Rangesum Issue

Gysbert_Wassenaar — Thu, 23 Jan 2014 19:08:39 GMT

That's because you're using the expression in a straight table that also has Claim No as dimension. So you get a distinct count per Claim No value. Simply remove Claim No from the straight table or use the expression in a text box.

Re: Rangesum Issue

Thu, 23 Jan 2014 19:29:27 GMT

No Same Result, I Tried both, removing Claims from the Table and also the Text BOX. If you look at the table when I remove the Claim No, its giving me the Number of COunts for each Occurence No, its not doing the Distinct

Re: Rangesum Issue

Gysbert_Wassenaar — Thu, 23 Jan 2014 19:49:27 GMT

Please post examples of the source data and of the result table you want. I don't think I understand what you're actually trying to achieve.

Re: Re: Rangesum Issue

Fri, 24 Jan 2014 20:19:48 GMT

Thnx for your Continous Help, I am almost there but still missing some Claims Numbers . Attached File will give you a better picture of what I am talking about. For Example

In Occurence Number Column (6773), it has Multiple Claim Numbers Assigned to it. I want the some of Net Indemnity Paid (Addition of All Claims wether OPEN OR CLOSED" on that Occurence Number , If atleast One Claim No has Claim Status Code as " CLOSED".

I Hope I Explained it better now

Re: Re: Re: Rangesum Issue

Gysbert_Wassenaar — Fri, 31 Jan 2014 14:39:42 GMT

That's a completely different question. And in the sample data you posted all Occurrence No values have at least one claim with status CLOSED. So, wildly guessing once more as to what you might actually want... see attached qvw.

Re: Re: Re: Rangesum Issue

Tue, 04 Feb 2014 15:05:34 GMT

Luckily I was able to make it work as Below. I have one more issue though.

Count

({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'}>} DISTINCT [Occurence No]))

Totally Different Issue

How Do I do tihs in SET Analysis

If(NET_INDEMNITY_PAID>1000000,1000000)

Re: Re: Re: Rangesum Issue

Gysbert_Wassenaar — Tue, 04 Feb 2014 15:08:50 GMT

You don't. Set analysis can be used to filter, but cannot be used for branching. But you can use rangemin(NET_INDEMNITY_PAID,1000000) instead of your if statement.

Re: Re: Re: Rangesum Issue

Tue, 04 Feb 2014 15:18:49 GMT

I also need to add the MAx Year to the statement and I am using the ValueList Dimension, is taht gonna work with it