Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Rangesum Issue

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


10 Replies

Re: Rangesum Issue

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

See attached qvw for an example.


talk is cheap, supply exceeds demand
Not applicable

Re: Re: Rangesum Issue

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Rangesum Issue


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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Re: Rangesum Issue

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Re: Re: Rangesum Issue

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

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.


talk is cheap, supply exceeds demand
Community Browser