How to find maximum value of an aggregated expression?

[#1]

Hi All

I have the following graph with

Dimensions: Country, GLOBAL_ITEM_ID and Yr_Wk_Frcst and I need to output the maximum value of the 2 expressions in a texbox:

I need edit below expression to retrieve maximum value 8605 of Expression number 1:

sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter))"}, OPE_WEK=, OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter))"}, OPE_WEK=, OPE_YY=>}STK_QT),GLOBAL_ITEM_ID,Yr_Wk_Frcst))

I need edit below expression to retrieve maximum value 19.3 of Expression number 2:

(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}STK_QT),GLOBAL_ITEM_ID,Yr_Wk_Frcst))/sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS),GLOBAL_ITEM_ID,Yr_Wk_Frcst)))*5

Can you please help?

[#2]

Great!! THis one is working and the correct value 8605 is output

[sunny_talwar]

For the second one,try this:

Max({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"},OPE_WEK=, OPE_YY=>}

Aggr(

(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"},OPE_WEK=, OPE_YY=>}STK_QT),GLOBAL_ITEM_ID,Yr_Wk_Frcst))/Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"},OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS),GLOBAL_ITEM_ID,Yr_Wk_Frcst)))*5

, Country, GLOBAL_ITEM_ID, Yr_Wk_Frcst))

[#4]

I've tried it and i'm getting a syntax error maybe a parentheses missing somewhere.. im lookign for it

[#5]

Wow I cant find it.. its quite complex.. Please guide? Its telling me error: Aggr takes at least 2 parameters

[sunny_talwar]

May be too many Aggr(), try this:

=Max({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}

Aggr(

(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}

Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}STK_QT),GLOBAL_ITEM_ID,Yr_Wk_Frcst))/

Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}

Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS),GLOBAL_ITEM_ID,Yr_Wk_Frcst)))*5

, Country, GLOBAL_ITEM_ID, Yr_Wk_Frcst))

[swuehl]

I think you need to add the Country dimension also to your inner aggr() functions, like

=Max({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}

Aggr(

(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}

Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}STK_QT),Country, GLOBAL_ITEM_ID,Yr_Wk_Frcst))/

Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}

Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS),Country, GLOBAL_ITEM_ID,Yr_Wk_Frcst)))*5

, Country, GLOBAL_ITEM_ID, Yr_Wk_Frcst))

Maybe you also need to change your aggregation scope, I guess you might want in fact something like

=Max({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=,OPE_YY=>}

Aggr(

sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}STK_QT)

/

sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS)

*5

, Country, GLOBAL_ITEM_ID, Yr_Wk_Frcst))

If all these suggestions don't work, could you please provide a small sample QVW to look at? It's hard to help with these kind of complex expressions without seeing the full picture.

[#8]

I have tried it and its giving me the value of 19.94. But the actual max value output must be 27.6

[#9]

Hi Swuehl, I have tried it and its giving me the output value 30.7 instead of 27.6

[#10]

Many thanks to you all guys Sunny and Swuehl I have modified a bit the expressions which you sent me as below and now im getting the correct value of 27.6

=Max(
{<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter) )"}, OPE_WEK=,OPE_YY=>}
(Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter))"}, OPE_WEK=, OPE_YY=>}STK_QT),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country) /
Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter))"}, OPE_WEK=, OPE_YY=>}SHIPMENT_AVG_3_WEEKS),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country)
)*5
)

[swuehl]

Glad you've worked it out.

Seems like you needed to change the date range?

Or does my last expression with the changed date range does not return the same as your last expression (just for interest, I would assume it should return the same, but without knowing the full setting, I might be wrong)?