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]
)))))))))
