Performance: Advanced Aggregation Dimension or Expression

Wed, 04 Apr 2012 14:57:06 GMT

Hi all,

I would like to find out what is my best possible approach to improving performance in my report.

I've identifed that the cause of the poor performance is an aggregation function that I use. I've listed this below for reference sake

=if(CostCentreStructureDesc='South Africa',
     sum(aggr(
                if(max({$<FiscalSetDesc={'$(vForecastFiscalSet)'}>}FiscalPeriodYTD)>=PeriodValue,
                     -sum({$<FiscalSetDesc={'$(vForecastFiscalSet)'},
                           CostCentreStructureDesc={'SouthAfrica'},
                           CurrencyCode={'ZAR'}>}Value),
                     -sum({$<FiscalSetDesc={'$(vForecastFiscalSetPrevious)'},
                           CostCentreStructureDesc={'SouthAfrica'},
                           CurrencyCode={'ZAR'}>}Value)),CostCentreStructureDesc,CostCentreDescStructure,Period))

/if(vCurrencyCode='USD',$(vCurrencyValue),1)/1000,

if(CostCentreStructureDesc='United Kingdom',
     sum(aggr(
                if(max({$<FiscalSetDesc={'$(vForecastFiscalSet)'}>}FiscalPeriodYTD)>=PeriodValue,
                     -sum({$<FiscalSetDesc={'$(vForecastFiscalSet)'},
                           CostCentreStructureDesc={'UnitedKingdom'},
                           CurrencyCode={'GBP'}>}Value),
                     -sum({$<FiscalSetDesc={'$(vForecastFiscalSetPrevious)'},
                           CostCentreStructureDesc={'UnitedKingdom'},
                           CurrencyCode={'GBP'}>}Value)),CostCentreStructureDesc,CostCentreDescStructure,Period))

*if(vCurrencyCode='USD',$(vCurrencyValue)/12.5,12.5)/1000))

Essentially what this expression is doing is splitting between 2 locations, South Africa and United Kingdom and then identifying that as soon as the current fiscal set period is reached that it will use the Previous Fiscal Set for the ROY (rest of year) calculation. The splitting between the 2 fiscal sets is VERY IMPORTANT. This expression is causing my report to slow down dramatically and wondering what is the best approach to improving performance.

The only other solution i have thought about is creating an artificial dimension using the aggr function and calling the dimension in my expression. Any ideas/Tips

PS: My scripting skills are even worst then my front-end but if you believe this is best handled in the script, please let me know

Thanking you all in advance

Regards,

Byron

Performance: Advanced Aggregation Dimension or Expression

Wed, 04 Apr 2012 15:23:25 GMT

Without spending a significant amount of time figuring out what exactly your expressions are doing, if you can do them in the script you are probably far better off as far as performance goes. As far as the performance between a huge expression and a huge dimension goes, I think you're probably better off using the huge expression. When I tried to make huge dimensions, the performance was ungodly slow. The expression was still really slow... but much better than the dimension was. But again... I'd give the script a shot.

topic Performance: Advanced Aggregation Dimension or Expression in QlikView

Performance: Advanced Aggregation Dimension or Expression

Performance: Advanced Aggregation Dimension or Expression