help improve performance of bar chart using AGGR() and cycle groups

[kmstephenson]

Hi All,

I have a bar chart with 2 dimensions, first- a cycle group that includes payment category fields (4 different levels of granularity), procedure code, and primary and secondary diagnosis. The second dimension groups the bars into 3 different organization types.

The expression of this chart measures utilization PMPY for the cycle group (PaymentCatClmTypeDx2) using this code-

IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='PaymentCategoryAggr',COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='PaymentCategoryMid',COUNT(AGGR(PaymentCategoryMid,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryMid))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='PaymentCategoryGrnlr',COUNT(AGGR(PaymentCategoryGrnlr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryGrnlr))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='Procedure',COUNT(AGGR(Procedure,KeyPatEnd,ClmFromDt, ClmThruDt, Procedure))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='PaymentCategoryOth',COUNT(AGGR(PaymentCategoryOth,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryOth))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='ClmType',COUNT(AGGR(ClmType,KeyPatEnd,ClmFromDt, ClmThruDt, ClmType))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='PrimaryDxCCSMulti',COUNT(AGGR(PrimaryDxCCSMulti,KeyPatEnd,ClmFromDt, ClmThruDt, PrimaryDxCCSMulti))/SUM(TOTAL(MemberMonths))*12,
IF(GETCURRENTFIELD(PaymentCatClmTypeDx2)='SecondaryDxCCSMulti',COUNT(AGGR(ClmType,KeyPatEnd,ClmFromDt, ClmThruDt, SecondaryDxCCSMulti))/SUM(TOTAL(MemberMonths))*12))))))))

The chart is frozen during the load and not loading. Occassionally, after a very long period of time the chart will load but making any selections causes the entire application to freeze again. What can I do to improve performance? Is this too much for a QliKView chart to handle?

[Gysbert_Wassenaar]

Try replacing that expression with eight expressions and show/hide those as needed. See this blog post for more information: How Not to Choose an Expression | Qlikview Cookbook‌. This one may be of interest as well: How to Choose an Expression | Qlikview Cookbook

[kmstephenson]

Thank you for the response and sorry I didn't see this sooner! I created expressions for each of the cycle groups:

For example-

Expression:

COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr))/SUM(TOTAL(MemberMonths))*12

Conditional:

vPaymentCategoryAggr

This is set to =GETCURRENTFIELD(PaymentCatClmTypeDx2)='PaymentCategoryAggr'

I did this for each of the 8 cycle group expressions, and an additional 8 pop up expressions to display additional information in the pop up.

For example:

='Utilization PMPY= '&
IF((COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr))/SUM(TOTAL(MemberMonths))*12)<1,
NUM(COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr))/SUM(TOTAL(MemberMonths))*12,'###,###.00'),
NUM(COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr))/SUM(TOTAL(MemberMonths))*12,'###,###.'))&chr(10)&
'Total Utilization= '&NUM(
COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr)),'###,###.')&chr(10)&
'# = '&$(vTotal#)

I'm still having some performance issues though. The charts are slow to load.Do you have any other suggestions for what might help speed up the new code?

Thank you!!

[maksim_senin]

Hi,

I'm not sure someone could help you without understanding of your model (this depends on many parameters including data scheme, data size, length of inter-table links, etc.), but general recommendation of best practices regarding performance says to perform all possible calculations in a load script.

So I'd recommend you to think about pre-calculation of neccesary values for the dimension(s).

Best regards,

Maxim

[kmstephenson]

Is there a function that does the equivalent in the load script? I am using aggregation that changes for each cycle group so I don't see how this can be computed correctly in the load script. I tried moving this back to SAS (where I created my input datasets), but the aggregation is static and was incorrect because it did not update correctly when my filters changed.