Chart performance

jarno_loubser — Mon, 17 Jan 2011 14:10:44 GMT

I have chart performance issues, while the data size (3M) in my model is not large nor is it complex (3 tables - 1 fact). I am doing some aggregation because the logic cannot be determined up front - ie backend. The complexity come in because sales figures in these chats must only be taken into account per store if the store had sales figures in both minimum and maximum periods.

I'm hoping to get some pointers on how to optimise these charts:

Here is the expression on one of my Gauge charts...

















//Get Same Store Maximum Period
//Only mapped stores
sum

 (if(isnull([Master Outlet Description]
),0,
//only same store

 if($(TransPeriod) < $(OpenPeriod),0,if($(TransPeriod) > $(ClosePeriod)
,0,
if

 ((aggr(NODISTINCT(sum(if($(TransPeriod) = MaxPeriod,[Dispatch Quantity]*[Digistics Convertion Factor],0))), [Master Outlet Description]
)) = 0,0,
if

 ((aggr(NODISTINCT(sum(if($(TransPeriod) = MaxPeriodMin1Year,[Dispatch Quantity]*[Digistics Convertion Factor],0))), [Master Outlet Description]
)) = 0,0,
//Only Max Period
if

 ($(TransPeriod) <> MaxPeriod
,0,
//Only certail category
if

 ([Product Category]<>'Pies',0,if([Product Halaal Status]
<>'NON-HALAAL',0,
[Dispatch Quantity]

 *[Digistics Convertion Factor]
)))))))))
/
//Get Same Store Maximum Period
//Only mapped stores
sum

 (if(isnull([Master Outlet Description]
),0,
//only same store

 if($(TransPeriod) < $(OpenPeriod),0,if($(TransPeriod) > $(ClosePeriod)
,0,
if

 ((aggr(NODISTINCT(sum(if($(TransPeriod) = MaxPeriod,[Dispatch Quantity]*[Digistics Convertion Factor],0))), [Master Outlet Description]
)) = 0,0,
if

 ((aggr(NODISTINCT(sum(if($(TransPeriod) = MaxPeriodMin1Year,[Dispatch Quantity]*[Digistics Convertion Factor],0))), [Master Outlet Description]
)) = 0,0,
//Only Max Period
if

 ($(TransPeriod) <> MaxPeriodMin1Year
,0,
//Only certail category
if

 ([Product Category]<>'Pies',0,if([Product Halaal Status]
<>'NON-HALAAL',0,
[Dispatch Quantity]

 *[Digistics Convertion Factor]
)))))))))

Chart performance

maneshkhottcpl — Mon, 17 Jan 2011 14:39:40 GMT

Hi,

If u avoid the use of aggr function then ur performance can improve.

Chart performance

jarno_loubser — Mon, 17 Jan 2011 14:50:25 GMT

I agree, but is there another way to exclude stores from sales data analysis where the store does not have sales movement in both the minumum and maximum period selected?

topic Chart performance in QlikView

Chart performance

Chart performance

Chart performance