topic Sum field by another distinct field in QlikView

Sum field by another distinct field

Thu, 21 Oct 2010 13:52:48 GMT

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 ID	CPT	Time Frame	Number of Rejections	Number of Denials
1	10	T1	2	0
1	10	T2	2	0
1	12	T1	2	0
1	12	T2	2	0
1	12	T3	2	0
1	12	T4	2	0
1	13	T1	2	1
2	50	T1	0	2
2	50	T2	0	2
2	50	T3	0	2
2	51	T1	0	0
2	51	T2	0	0
2	51	T3	0	0
2	52	T1	0	0
3	40	T1	1	1
3	40	T2	1	1
3	40	T3	1	1

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

Sum field by another distinct field

Thu, 21 Oct 2010 14:23:22 GMT

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?

Sum field by another distinct field

Thu, 21 Oct 2010 14:40:39 GMT

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.

Sum field by another distinct field

Thu, 21 Oct 2010 14:49:44 GMT

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

Sum field by another distinct field

Thu, 21 Oct 2010 14:59:21 GMT

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.

Sum field by another distinct field

Thu, 21 Oct 2010 15:31:09 GMT

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.

Sum field by another distinct field

Thu, 21 Oct 2010 15:50:23 GMT

HI,

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

sum([Number of Rejections])

Re: Sum field by another distinct field

Thu, 02 Aug 2012 08:46:20 GMT

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.