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,
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.
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
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".
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.