Rangesum Issue

[#1]

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

[Gysbert_Wassenaar]

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

See attached qvw for an example.

[#3]

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

[Gysbert_Wassenaar]

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.

[#5]

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

[Gysbert_Wassenaar]

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.

[#7]

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

[Gysbert_Wassenaar]

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.

[#9]

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)

[Gysbert_Wassenaar]

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.