Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
Showing results for 
Search instead for 
Did you mean: 

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

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?

4 Replies

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

talk is cheap, supply exceeds demand

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

For example-


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



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= '&
COUNT(AGGR(PaymentCategoryAggr,KeyPatEnd,ClmFromDt, ClmThruDt, PaymentCategoryAggr)),'###,###.')&chr(10)&
'# = '&

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!!



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,



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.