Set Analysis / Aggregation - Summing While Ignoring Chart Dimensions

[#1]

Hi,

This is my first time posting here. I've been using QlikView for about six months and love it. However, there are times when I just can't seem to get the results I want. I've scoured the forums in an attempt to answer the question myself but couldn't find something that directly related to my issue. If there is a solution already posted I apologize and would appreciate it if someone could point me to it. So, here goes!

I've got a data set that looks something like this (I apologize for the comma-separated list, I tried to make it look prettier but ran out of width in the post):

TX_ID, Account, Service Date, Detail Type, Adj Code, Adj Category, Amount, %Charge_Flag
1, 100, 01-Jan-2010, Charge, Null, Null, 150, 1
1, 100, 01-Jan-2010, Adjustment, 1080, Charity, -50, 0
1, 100, 01-Jan-2010, Adjustment, 1041, Contractual, -30, 0

I'd like to build a pivot table chart that shows dimensions, expanded as follows:
Adjustment Category -> Adj Code -> Account -> Service Date

The expressions for each row are supposed to be like these:
Sum of total charges across the entire transaction (TX_ID)
Sum of adjustment dollars for each adjustment code

The pivot table (fully expanded) should look like this when I'm done:

Adj Category Adj Code Account Service Date Charge Adj Amount
Charity 1080 100 01-Jan-2010 $150 ($50)
Contractual 1041 100 01-Jan-2010 $150 ($30)

The problem is that I'm having a not-so-good time trying to get the charge to display on each
row of the pivot table. I don't have the original charge amount on each row of the data, otherwise
this would be easy. I figured I needed to do set analysis to get the value but that doesn't
work because the charge row is not included in the data that makes up my pivot table.

I tried using the AGGR() function and thought I had it working but ended up with an odd
result. Here is the AGGR() function I used:

Aggr(Max(%Charge_Flag * AMOUNT), TX_ID)

There is only one charge per TX_ID so I thought that by aggregating over the whole TX_ID I
would get the charge. However, my pivot table now displays like this:

Adj Category Adj Code Account Service Date Charge Adj Amount
Charity 1080 100 01-Jan-2010 $150 ($50)
Contractual 1041 100 01-Jan-2010 - ($30)

Note the NULL in the Charge column on the second row (Contractual).

Is it possible to get the sum that I want here, or do I need to adjust my model to include
the original charge on each row of my fact table?

Thanks!

Best Regards,
Chris

[pover]

Chris,

Try something like sum({$<[Detail Type]={'Charge'}>} Total <Account,[Service Date]> Amount) for you Charge column.

I don't understand completely how you want the pivot table to look if you have more than one TX_ID, but maybe the formula will work.

Regards.

[#3]

Hi Karl,

Thanks so much for the quick response! I took your suggestion and adapted it to my chart. This is the expression I came up with:

Sum({$<DETAIL_TYPE = {'1'}>} Total <ACCOUNT_ID, ORIG_SERVICE_DATE> AMOUNT)

What's interesting is that while this expression appears to now calculate the charge correctly for each row in the pivot table there is now an extra row in my pivot table. This row has nulls for the Adjustment Category and Adjustment Code. I am assuming that this is the data from the charge line in my data set (with the nulls for the adjustment category and code). I don't want this to appear so I went to the adjustment category and adjustment code columns in the pivot table and set them to 'Suppress When Value Is Null'. This did indeed remove the line but it had the unfortunate effect of now evaluating my new charge expression to $0.00 for every row.

With new expression, before suppressing nulls:

Adj Category Adj Code Account Service Date Charge Adj Amt
Charity 1080 100 01-Jan-2010 $150 ($50.00)
Contractual 1041 100 01-Jan-2010 $150 ($30.00)
- - 100 01-Jan-2010 $150 ($0.00)

With new expression, after supressing nulls:

Adj Category Adj Code Account Service Date Charge Adj Amt
Charity 1080 100 01-Jan-2010 $0 ($50.00)
Contractual 1041 100 01-Jan-2010 $0 ($30.00)

The expression for Adj Amt, if relevant, is this:

Sum({$} AMOUNT)

If I have more than one TX_ID in my data set (which I do, there are plenty of transactions) the expression should roll up all the charges across all the transactions that involve that particular adjustment code. The expression you came up with seems to do this fine and I can expand out the 'Total <fields>' section if I need to add more dimensions. The challenge now is eliminating the line in the pivot table that has no relation to the adjustment codes.

Any thoughts there?

Best Regards,

Chris

[#4]

Hi Chris, try adding the following to the expression:

Sum({$<DETAIL_TYPE = {'1'}, [Adj Category] = {"*"}, [Adj Code] = {"*"}>} Total <ACCOUNT_ID, ORIG_SERVICE_DATE> AMOUNT)

Regards.

[#5]

Hi Ivan,

Thanks for the suggestion. Adding that filter to the set analysis did remove the extra line from my pivot table but it also made my charge expression evaluate out to $0.00.

If I am interpreting the expression into "English" correctly I read it like this: For the current selection, but only those rows where Detail Type =1, Adj Category is (some value) and Adj Code is (some value), provide me a total sum of AMOUNT grouped by Account ID and Original Service Date.

The set analysis piece is taking out my row that has the charge because that row has a null Adj Category and Adj Code. Therefore, the sum of charges on the remaining rows is going to be $0.00 because those are adjustments, not charges.

I think we're on the right track, I'm just struggling in translating what I need into QlikView-speak. I tried changing the set analysis to be something like this, trying to capture the NULL in the Adj Category and Adj Code fields but it still evaluated to $0.00 (MATCH_RPT_GRP_TEN_NAME is Adj Category, MATCH_PROC_CODE is Adj Code):

Sum({$<DETAIL_TYPE = {'1'}, MATCH_RPT_GRP_TEN_NAME -= {'*'}, MATCH_PROC_CODE -= {'*'}>} Total <ACCOUNT_ID, ORIG_SERVICE_DATE> AMOUNT)

[jonathandienst · Accepted solution]

Chris

I think this works. This is based on the data and field names in your original post.

Jonathan

[#7]

Thanks to all of you for the assistance!

Chris