Hi all,
Is there a better way of approaching this challenge - as my "solution" is too resource heavy?
- Have product sales by month, by raw material
- Pricing table by month, by raw material
- Approximately 8million records
Need to apply raw material pricing to product sales - whilst allowing the user to offset this pricing by X months in the user interface (inventory lag)
User then defines (in interface) a base pricing period for which all monthly prices are compared against (the offset is applied)
Actual / Offset Price:
To reduce UI calculations/formulas, returned raw material price to sales month, and then created 3 offset price columns within Sales table:
, PriceKGoffset1, PriceKGoffset2, PriceKGoffset3
if(RowNo()<=1 or PmtKey2<>previous(PmtKey2),0,rangesum(peek(PriceKG,-1),0)) as PriceKGoffset1,
Base Period definition:
- Dropdown month selection in user interface
- Dropdown offset value selection (0-3) in user interface - although ideally should be more flexible/greater
- Variables created for both above, with final period variable created:
vDateNum1 =vDateMax1-vOffset
Table Structure:
Main Sales Table
Pricing table (actuals are mapped to sales table, base prices are linked by material key (not period)
Base Price:
Created/defined via set analysis:
min({<PeriodPriceNum = {'$(vDateNum1)'} >}PriceKG2))
To "ease" report creation, final impact calculations are defined in variables (4 variables created, 1 for each of the 4 offsets (0-3))
exp_Impact0, exp_Impact1, exp_Impact2, exp_Impact3
exp_Impact1 =
sum(aggr(
(PriceKGoffset1 - min({<PeriodPriceNum = {'$(vDateNum1)'} >}PriceKG2)) * SQM_ActualsCalc *Grams/1000
,Material,ProductCode,Period ))
Graphs/UI
Based off vOffset selection, conditional show of variable expression exp_Impact0, exp_Impact1, exp_Impact2, exp_Impact3
When applied against 8million records, the above takes a "while" to re-calculate following new selections...
Can the above approach be modified to make more efficient?
Is there a way to avoid having to create separate formulas for each of the offsets?
Any suggestions greatly appreciated,
Kind regards,
Rich
