topic Aggr in Chart Expression in QlikView

Aggr in Chart Expression

byrnel0586 — Wed, 17 Aug 2016 21:07:11 GMT

Hi,

I currently have this expression which works and shows the "Star" values based on the current selection:

Aggr($(vStarWeight),Star,MeasureID))

This is working fine, but now they want to be able to see a specific physician's performance compared to the entire market's. So, the Star value for the physician as a bar and the market's overall Star value as a line on the same chart. How can I create two different expressions that will show what they are asking? I've tried several things and the values keep showing as the same.

Any help is appreciated!

Re: Aggr in Chart Expression

swuehl — Wed, 17 Aug 2016 21:15:50 GMT

It would be helful to know more about the context of the expression (dimensions / selections), your data model and the variable definition.

In general, take care to

Use Aggregation Functions!

And to consider

Pitfalls of the Aggr function

Maybe try an outer aggregation with the TOTAL qualifier like

=Avg(TOTAL Aggr($(vStarWeight),Star,MeasureID))

for the market average star value.

Re: Aggr in Chart Expression

vishsaggi — Wed, 17 Aug 2016 21:17:07 GMT

Can you share a sample app if possible please ? And can you show us the expression in your vStarWeight variable ?

Re: Aggr in Chart Expression

byrnel0586 — Thu, 18 Aug 2016 13:03:17 GMT

Thanks! I will try that.

The variable definition is: IF(SUM(NUMERATOR)/SUM(DENOMINATOR)>=PtileBeg and SUM(NUMERATOR)/SUM(DENOMINATOR)<= PtileEnd
,Star)

The current dimension is MeasureID. Basically, when a physician or vendor is selected they would like to see how they are doing compared to the overall Market. I am pretty sure each physician is only associated to one market, but I have a feeling a problem may be that each physician is not eligible for every MeasureID, while the Market would likely have a value for every MeasureID.

Re: Aggr in Chart Expression

byrnel0586 — Thu, 18 Aug 2016 13:04:13 GMT

The variable definition is: IF(SUM(NUMERATOR)/SUM(DENOMINATOR)>=PtileBeg and SUM(NUMERATOR)/SUM(DENOMINATOR)<= PtileEnd
,Star)

Unfortunately I don't have a sample app I can share. Mine is way too big.

Re: Aggr in Chart Expression

byrnel0586 — Thu, 18 Aug 2016 13:14:53 GMT

I am trying the TOTAL qualifier as a line, but it is just giving me the avg of the values of the bars across the chart. I have attached a screenshot, maybe that will help illustrate what I am needing. For instance, when PCP 1049213 is selected, I want to see the overall Market of North Florida's star performance for each measure.

Re: Aggr in Chart Expression

swuehl — Thu, 18 Aug 2016 14:51:16 GMT

Maybe use set analysis to select your market based on PCB selection and clear selection on that specific PCB:

IF(

SUM({<MarketField = p(), PCBField =>}NUMERATOR)/SUM({<MarketField = p(), PCBField =>} DENOMINATOR)>=PtileBeg

and

SUM({<MarketField = p(), PCBField =>}NUMERATOR)/SUM({<MarketField = p(), PCBField =>}DENOMINATOR)<= PtileEnd
,Star)

So basically change the

The Aggregation Scope

But sttill not sure how your expression really works and how your data model looks like.

Re: Aggr in Chart Expression

byrnel0586 — Thu, 18 Aug 2016 15:02:37 GMT

I have attached the data model.

The Star value is calculated depending on where the rate (numerator/denominator) falls on the PlanLevel_Thresholds table. The StarMember_Details table houses all of the data that is needed for this calculation. What the expression currently is doing is aggregating all of the data and giving an overall Star value by MeasureID, depending on the selection.

Sorry if I am not explaining correctly! Thanks for all of your help.