Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Dimension rows all showing total calculation, rather than row level

I am trying to build a table which compares a Doctors mix of certain Procedures to the same mix of procedures for all doctors nationally who have the same Taxonomy ( Specialty ) code.

I'm able to successfully calculate each Doctors value, but the National value is always showing up as the total for the entire set, as long as I have made no selection by the Doctor.  If I make a selection on a Dr., then the National value is correct for that Doctor, I assume because then the total is the same as the selection.

My data is split through the field called %Source.  In our application, %Source is always selected as 'My Data'.  The rows where %Source = 'Market Data' represents the National data that I'm trying to compare to.

I've attached a QVW with sample data that displays this behavior.

I've tried experimenting with various combinations of the aggr function, the TOTAL keyword, etc.  If I take out the TOTAL keyword on the outer SUM, then none of the national values are correct.  This is the closest I've been able to get, and I'm out of ideas/QV knowledge.

In the table "E&M National Outliers"

What I currently have:

DoctorMy ProfileNational
Dr. One

97.1429%

110.0800%
Dr. Two150.0000%110.0800%
Dr. Three134.8571%110.0800%

What would be correct:

DoctorMy ProfileNational
Dr, One97.1429%113.2200%
Dr. Two150.0000%128.7800%
Dr. Three134.8571%102.1800%

Any help would be greatly appreciated.

1 Solution

Accepted Solutions
Not applicable

Re: Dimension rows all showing total calculation, rather than row level

Based on swuehl's comment about the set analysis expression only being evaluated once per chart, I searched around and found the article on Evaluating Sets in the Context of a Dimension ( http://community.qlik.com/docs/DOC-1335).

I know this technique works for a small number of Physician Names, and I'm working on getting it generalized in my load script for a large set of names to see if it performs well.

3 Replies
MVP
MVP

Re: Dimension rows all showing total calculation, rather than row level

The issue here is that the set analysis is only evaluated once per chart, not per dimension line.

And you need to select a doctor, thus the taxonomy codes will correctly be set using the p() function, right?

At the moment, I don't see an easy answer here, and you may have to consider changing your data model to support your analysis.

Not applicable

Re: Dimension rows all showing total calculation, rather than row level

Thank you swuehl.

The timing of the set analysis evaluation makes sense given the behavior I'm seeing.

Unfortunatley, changing the data model is really not possible since we have a rather large production application already using it. This functionality is viewed as very useful for our users, but probably not at the expense of modifying the data model.

Not applicable

Re: Dimension rows all showing total calculation, rather than row level

Based on swuehl's comment about the set analysis expression only being evaluated once per chart, I searched around and found the article on Evaluating Sets in the Context of a Dimension ( http://community.qlik.com/docs/DOC-1335).

I know this technique works for a small number of Physician Names, and I'm working on getting it generalized in my load script for a large set of names to see if it performs well.

Community Browser