How to use Aggr?

[#1]

I've been looking information given about the aggr function but it still hasn't quite hit me yet. I also have not been able to find very many examples to see how this works.

Can anyone give me an explanation of what this functions does or point me in a direction with material covering this more thoroughly?

The reason why I ask is I've run into a situation. I have doubly pivoted data and I need to sum [Number of Denials] by a specific [TimeFrame] = {'Time Frame 1'}, [Claim ID], [CPT], ID, and ID3. I don't believe I can do this with set analysis, hence why I'm looking into aggr().

Thanks!

[#2]

Think of Aggr() as an on the fly Group By. You define your dimensions and QlikView will aggregate over those dimensions. For example, to count stores that meet a sales threshold, you would do something like:

Count(Aggr(Sum(Sales), Store) > 5000)

That's a simplified example.

You are required to use Aggr() when doing nested aggregates. In the previous example, I wanted a Sum inside a Count, making the Aggr() a requirement.

I don't exactly follow your final expression question. Perhaps you can show what you were trying to use?

[#3]

For my dimension, I'm using a statement: ='Number of Rejections:' .

I've used something like this before for a count.

count({$<[Claim Status ICD]={'Accepted'}>} distinct [Claim ID ICD] &'_'& [CPT ICD]&'_' &ID)

But the situation is different because I want to sum a field that already contains frequencies ([Number of Rejections]). I need to make sure I get the frequencies from specific rows using indicator variables because the data has been pivoted twice.

I've tried using something like

sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>} aggr(sum([Number of Rejections),[Claim ID], ID, ID3, [CPT])

But it doesn't return any results.

[#4]

I've also thought maybe I do need set analysis where I could use something like this:

sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>} [Number of Rejections] distinct [Claim ID], ID, ID3, [CPT])

But this is improper syntax. 😕

[#5]

Well, first you're missing a closing parenthesis on that expression. I don't know if that's just a paste error, but I'd check that first. Another thing to try is to put the Set Analysis inside the interior Sum. I'd put it in both, but it may be worth a check to see what combination works. I think:

sum(aggr(sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>}
 [Number of Rejections), [Claim ID], ID, ID3, [CPT]))

May work.

I guess I'm not seeing why you need the two Sums. Wouldn't:

sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>} [Number of Rejections))

work?

When I've used Aggr() it is usually using different aggregate functions. I've never used Sum(Aggr(Sum())) before.

[#6]

The two sums with aggr comes from me not understanding what aggr does. Judging by your explanation of aggr, I don't think it's the best choice because I'm only trying to take the sum of a column as if the table were depivoted. I still want to keep the table pivoted for a different bit of analysis I've already done.

And the 2nd would not work because the data is pivoted twice. I attached a view of the table. The code I used to pivot them are:

ICDTable:
CROSSTABLE (ICDVariable, ICDNumber, 31)
LOAD recno() as ID, [ClaimDenial], [Taxonomy Category], [Date of Service], [CPT Category], [CPT], [CPT Descriptions], [Claim Status], [Facility Name], [Customer Name], [Provider Name], [Payer Name], [Claim ID],
[Submitted Amount], [Paid Amount], Allowed, Reductions, Reversals, [Other Reductions], [Payer Initiated Reductions], Deductible, Coinsurance, [Number of Rejections], [Number of Denials],
[Time Frame 1], [Time Frame 2], [Time Frame 3], [Time Frame 4], [Time Frame 5], [Time Frame 6], [Time Frame 7],
ICD1, ICD2, ICD3, ICD4, ICD5, ICD6, ICD7, ICD8
Resident Prototype;

ICD:
LOAD text(trim([ICD Number])) as ICDNumber, [ICD Description], [ICD Category]
FROM (ooxml, embedded labels, table is ICD);

Left Join (ICDTable)
Load [ICDNumber], [ICD Description], [ICD Category] Resident ICD
Where exists([ICDNumber]);

TimeFrame:
CROSSTABLE (TimeFrame, Days, 29)
LOAD recno() as ID3, ID as [ID ICDPivot], [ClaimDenial] as [ClaimDenial Time], [Taxonomy Category] as [Taxonomy Category Time], [Date of Service] as [Date of Service Time],
[CPT Category] as [CPT Category Time], [CPT] as [CPT Time], [CPT Descriptions] as [CPT Description Time], [Claim Status] as [Claim Status Time],
[Number of Denials] as [Number of Denials Time], [Facility Name] as [Facility Name Time], [Customer Name] as [Customer Name Time], [Provider Name] as [Provider Name Time],
[Payer Name] as [Payer Name Time], [Claim ID] as [Claim ID Time], [Submitted Amount] as [Submitted Amount Time], [Paid Amount] as [Paid Amount Time],
Allowed as [Allowed Time], Reductions as [Reductions Time], Reversals as [Reversals Time], [Other Reductions] as [Other Reductions Time],
[Payer Initiated Reductions] as [Payer Initiated Reductions Time], Deductible as [Deductible Time], Coinsurance as [Coinsurance Time], [Number of Rejections] as [Number of Rejections Time],
[ICD Description] as [ICD Description Time], [ICD Category] as [ICD Category Time], [ICDVariable] as [ICDVariable Time], [ICDNumber] as [ICDNumber Time],
[Time Frame 1], [Time Frame 2], [Time Frame 3], [Time Frame 4], [Time Frame 5], [Time Frame 6], [Time Frame 7]
Resident ICDTable
Where ICDNumber > 0;

[#7]

Set analysis works just fine using TimeFrame='Time Frame 1' and ICDVariable = 'ICD1'. But this only works for 1 level of my analysis.

The other level I do need to have sum up [Rejections] and have sum of those defined by distinct combinations of [Claim ID], TimeFrame=TimeFrame1, and ICDVariable = 'ICD1'

[#8]

So you want the Sum of Rejections for each combination of ClaimId, TimeFrame and ICDVariable? Are there more than one record in your table for each combination? In other words, will multiple lines in your table share the same value for this expression?

If so, then you need the TOTAL <Field> modifier, not Aggr(). TOTAL <Field> is similar to a group by, but is used directly in an aggregate function. This modifier will ignore some dimensions to get the TOTAL grouped on the Fields defined within the <> brackets. Try this:

sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>}
 TOTAL <ClaimId> [Number of Rejections))

Since your TimeFrame and ICDVariable will be the same for every record on the table (anything else will be filtered with the Set Analysis), you only need to TOTAL on ClaimId.

If you do:

sum({$<TimeFrame={'Time Frame 1'}, [ICDVariable]={'ICD1'}>} TOTAL [Number of Rejections))

That should give you the grand total on every line.

[#9]

Almost that's making progress. I'm still struggling to get the numbers to match on a claim level. But that's because the scope changes up a little bit.

=sum({$<[TimeFrame]={'Time Frame 1'}, [ICDVariable Time]={'ICD1'}>} Total <[Claim ID Time]> [Number of Denials])
Works for the CPT level.

Let me explain a little. Consider the unpivoted data:

The number of rejections should be claim level but should be simplified to only one row. I say this because one claim can have multiple CPT codes and thus we'll see the number of rejections repeating because these values are based on the claim level.

The number of denials are CPT level but can be summed overall.

This is in unpivoted data. So for the CPT lvl in my pivoted data, we narrowed it down to a specific time frame and icd.

So I guess, to refine a bit, I'm looking for a sum of rejections. This would need to get down to the claim level, but I want to make sure it is not summing all the numbers within the claim if there are multiple CPT codes.

EDIT: I double checked with my excel value, and for the Number of rejections I should come up to a sum of 1,408 and a count of 702.

For Number of denials, i should come to a sum of 2,084 and a count of 1,028.