Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I’m doing some calculations comparing a customer’s product mix to a standard model.
So far I have a chart with the product lines as the Dimension and the Expression sums the Net Sales
Expression = sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)
ProdLine Net
A 7,441
B 15,054
C 703
D 162
Then I need to divide the net sales of each product line by the standard model (this also functions as intended)
Expression = aggr(sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc)
ProdLine Net / StdMdl
A 7,441 0.4575 = 1,626
B 15,054 0.1275 = 118,071
C 703 0.1175 = 5,986
D 162 0.2975 = 543
Here’s what I can’t get to work. I need to select the max value (118,071) and multiple it by the standard model
ProdLine MaxNet * StdMdl NewAmt
A 118,071 0.4575 = 54,017
B 118,071 0.1275 = 15,054
C 118,071 0.1175 = 13,873
D 118,071 0.2975 = 35,126
Then I need to subtract the original net sales from the newly calculated amount.
ProdLine NewAmt - StdMdl Result
A 54,017 7,441 = 46,576
B 15,054 15,054 = 0
C 13,873 703 = 12,423
D 35,126 162 = 34,964
These last four values (46576 , 0 , 12423 , 34964) are the only values that need to be displayed on the graph.
UPDATED
Step 1 : NetSales
Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)
Step 2: NetSales divided by StdMdl and Aggregated by ProdLineDesc for each ProdLine
Aggr(Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc)
Step 3: This will give Max value for 2nd Step
Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
Step 4 : Individual multiply by StdMdl
StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
Step 5: Step 4 - Actual NetSales
StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
-
Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)
So Step 5 is your required result...
Hope this will work...
Try:
Aggr(
(
Max( Total {< GapAnalysisFlag = {1} FiscalYear = {2013} >}
aggr(
sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)
/StdMdl
,ProdLineDesc)
)
*StdMdl)
)
- sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)
)
UPDATED
Step 1 : NetSales
Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)
Step 2: NetSales divided by StdMdl and Aggregated by ProdLineDesc for each ProdLine
Aggr(Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc)
Step 3: This will give Max value for 2nd Step
Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
Step 4 : Individual multiply by StdMdl
StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
Step 5: Step 4 - Actual NetSales
StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} FiscalYear = {2013} >} NetSales)/StdMdl,ProdLineDesc))
-
Sum({$< GapAnalysisFlag = {1} FiscalYear = {2013} >}NetSales)
So Step 5 is your required result...
Hope this will work...
BTW... your result of
ProdLine Net / StdMdl
A 7,441 0.4575 = 1,626
B 15,054 0.1275 = 118,071
C 703 0.1175 = 5,986
D 162 0.2975 = 543
is wrong for A....
Manish,
Thank you very much for your help. I am using the equasion you provided. However, I am having some trouble with a variation of it. I would like to change the " FiscalYear = {2013} " portion of the set analysis so that it is based on the previous 6 months from the current day. I have calculated the begining date and set it equal to a variable via the script: v6MonthsAgo = AddMonths(MonthStart (Today()),-6); I also have a Master Calendar containing the field "CalDate"
StdMdl* Max(TOTAL Aggr(Sum( {< GapAnalysisFlag = {1} CalDate >= v6MonthsAgo >} NetSales)/StdMdl,ProdLineDesc))
-
Sum({$< GapAnalysisFlag = {1} CalDate >= v6MonthsAgo >}NetSales)
How should this new equasion look?