Optimization Expression

[#1]

Hi all,

I have a chart that consume more ressources memory. I want to optimize this expression.

The Table that contains field KPI_Stock_PTF is a table Island with the rest of the model.

Dimension:

KPI_Stock_PTF

Expression: QTE

if(KPI_Stock_PTF='Total PTF',sum(STK_PTF_DISPO+STK_PTF_DIV+STK_PTF_IMMO+STK_PTF_PER+STK_PTF_PROMO+STK_PTF_QUAR),

if(KPI_Stock_PTF='Flux Tendu',sum({$ <FLUX={'FSDE'},  TYPEMARQUE={'MN','MS'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Flux Stocké',sum({$ <FLUX={'ENT'},  TYPEMARQUE={'MN','MS'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='MDD',sum({$ <TYPEMARQUE-={'MN','MS','MP-EXC'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Exclus',sum({$ <TYPEMARQUE={'MP-EXC'}, ID_FOUR-={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Consommable',sum({$ <ID_FOUR={104,200,262}>}STK_PTF_DISPO),

if(KPI_Stock_PTF='Quarantaine',sum(STK_PTF_QUAR),

if(KPI_Stock_PTF='Promotion',sum(STK_PTF_PROMO),

if(KPI_Stock_PTF='Divers',sum(STK_PTF_PER+STK_PTF_IMMO+STK_PTF_DIV)

)))))))))

Is that more optimize to put into the script and how ?

Thanks for your help.

[Anonymous]

I also build KPIs like this, where the table containing the dimensions is not connected to the rest of the datamodel. I've not seen any optimisation issues with it yet.

Perhaps comment out the various lines to see whether it is one or more of these KPIs that is specifically causing you performance problems. Are any of these KPIs returning an unfiltered/global result, suggesting a link is broken and the calculaton engine is taking time to sum up all of a fields the records.

Something else to watch for is where the fields used in a KPI's calculation are spread across too many links in the datamodel. For instance, for the Flux Tendu KPI, are FLUX TYPEMARQUE, ID_FOUR and STK_PTF_DISPO spread across the datamodel, or occuring on adjacent tables. QlikView should be able to handle spead out data of course, but may take longer to associate it.

Jonathan

[Gysbert_Wassenaar]

You could try a pick-match combination instead of the nested if's.

pick(match(KPI_Stock_PTF, 'Total' ,'Flux Tendu',..etc ,'Divers'), sum_expression_for_total_here, sum_expression_for_fluxtendu_here, ...etc, sum_expression_for_divers_here)

[#4]

Thank Jonathan.

Thank Gysbert for your expression but the memoy (KB) is the same. If function or Pikck function : 6128 KB.