Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum field by another distinct field

Hello,

I'm trying to write an expression along the lines of

sum the [Number of Rejections] for every distinct [Claim ID]. The data is pivoted so you'll see something along the lines of

Claim IDCPTTime FrameNumber of RejectionsNumber of Denials
110
T120
110T220
112T120
112T220
112T320
112T420
113T121
250T10
2
250T20
2
250T302
251T100
251T200
251T300
252T100
340T111
340T211
340T311

Where the number of rejections is Claim specific and the number of denials is cpt specific.

For this example, I'm looking to get a sum of the total number of rejections, which should be 3 (2 for claim 1, 0 for claim 2, and 1 for claim 3).

I was already able to get the number of denials by using set analysis something like (=sum({$<[TimeFrame]={'Time Frame 1'}, [ICDVariable Time]={'ICD1'}>} [Number of Denials Time]))

But I see it's not that simple for the claim level.

Any help would be greatly appreciated!

~Thanks

1 Solution

Accepted Solutions
Not applicable
Author

I dont know where you wanna apply this.....

but if you create a chart (dynamic table) with dimension is Claim ID with expression

=sum(DISTINCT [Number of Rejections])

show results what i think you want!

If not, can you explain more?

View solution in original post

7 Replies
Not applicable
Author

I dont know where you wanna apply this.....

but if you create a chart (dynamic table) with dimension is Claim ID with expression

=sum(DISTINCT [Number of Rejections])

show results what i think you want!

If not, can you explain more?

Not applicable
Author

The behavior that expression gives isn't what I hoped. If I have

claim no rejections

1 1

2 1

3 2

4 3

5 1

6 2

The value it returns is 6. Which means it's taking the unique values and adding those up, not adding the numbers up by unique claims.

My dimension is more of a label: ='Number of Rejections (Claim Level):' So really I'm just looking for one value to show at the end.

Not applicable
Author

I think i dont understand what you wanna...

I made this example ...i don´t know if it is helpfull for you...

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/8204.teste.zip:550:0]

I´ve sended zip file because here is blocked to send .qvw

Not applicable
Author

I can't view the file because I'm using the personal edition.

What I'm trying to do is write an expression that will select distinct values of Claim ID and then reference to the corresponding value in number of rejections... and then sum up those values.

I don't want it to subtotal the number of rejections by distinct claim then add up the subtotals.

Not applicable
Author

I started playing around with what you suggested, and I was able to come close to what I wanted, I posted a picture of what I'm looking at.

I used the [Claim ID] as the dimension and used the expression sum(distinct [Number of Rejections]).

I changed the totals label appropriately, but made the total, total by rows and not by expressions.

While this number is what I want, i don't want the actual data showing. Is there a way to hide that and only have the totals showing?

Or is there a better solutions?

EDIT: Nevermind, I figured this out too. I went to the presentation tab and checked on 'Max Number' and then entered '0' for the max number of fields.

Thanks for the suggestion.

Not applicable
Author

HI,

In Pivot table in dimensions you should add claim_id, in expressions

=



sum([Number of Rejections])

Not applicable
Author

Hi,

I have a similar situation.  However, I cannot put the "Claim ID" field as a dimension as I have over a 1 million rows of data.  Would this still work? SUM(DISTINCT [....])?

Thanks.