How to find max value of expressions starting from selected week?

[#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 which are active as from my max selected week (in example 2016-W5) till the max number of available weeks:

I need to edit below expression to retrieve maximum value (when I scroll the max value is 1833) of Expression number 1 active as from 2016-W5 in my example:

Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}OPTIMAL_STOCK_LEVEL),GLOBAL_ITEM_ID,Yr_Wk_Frcst))

I need edit below expression to retrieve maximum value (21.8) of Expression number 2 active as from 2016-W5 in my example:

(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}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=, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}Aggr(sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter)-2) <$(=max(Period_Week_Counter)+10)"}, OPE_WEK=, OPE_YY=, Country={"France"}, ITEM_PART_NUMBER=, ITEM_NAME=, RELATED_CWH_ITEM_CODE=, GLOBAL_ITEM_ID=P(GLOBAL_ITEM_ID)>}SHIPMENT_AVG_3_WEEKS),GLOBAL_ITEM_ID,Yr_Wk_Frcst)))*5

I need the max for all countries thereby I will remove the condition Country={"France"}

Can you please help?

[#2]

I have replaced both expressions with the fields which I need but for first expression Im getting max value 13315

and for 2nd one I get max value of 319.79

max first expression:

=Max({<Period_Week_Counter={">=$(=Max(Period_Week_Counter))"}, OPE_WEK=,OPE_YY=>}Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"},OPE_WEK=, OPE_YY=>}OPTIMAL_STOCK_LEVEL),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country))

max second expression:

=Max(
{<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}, OPE_WEK=,OPE_YY=>}
(Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}, OPE_WEK=,OPE_YY=>}OPTIMAL_STOCK_LEVEL),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country) /
Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}, OPE_WEK=,OPE_YY=>}ForcastValue),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country)
)*5
)

[sunny_talwar]

Can you put these expressions in straight table with the three dimension and share the screen shot of the table?

[#4]

Wooaah cool Thanks Sunny, I've edited your expression a bit by removing condition "OPE_WEK=, OPE_YY"

as follows and its working. I think I needed to remove it due to my data model works

=Max({<Period_Week_Counter={">=$(=Max(Period_Week_Counter))"}>}Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}>}OPTIMAL_STOCK_LEVEL),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country))

[#5]

Same for 2nd expression, I edited yours a bit as follows and it works:

=Max(
{<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}>}
(Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}>}OPTIMAL_STOCK_LEVEL),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country) /
Aggr(Sum({<Period_Week_Counter={">=$(=max(Period_Week_Counter))"}, OPE_WEK=,OPE_YY=>}ForcastValue),GLOBAL_ITEM_ID,Yr_Wk_Frcst, Country)
)*5
)

[sunny_talwar]

Awesome

[#7]

Thanks lots to you dear Sunny and Swuehl , now all the max functions requirements are working great in the application. Pretty cool!

Wishing you all a superb day ahead!