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?

[swuehl]

Expression1 could be like

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

[swuehl]

And your second maybe

=

Max( 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

,GLOBAL_ITEM_ID,Yr_Wk_Frcst))

or

=

Max( 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

,GLOBAL_ITEM_ID,Yr_Wk_Frcst))

[sunny_talwar]

May be these:

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

Max(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))

[#5]

THanks Dear Swuehl and Sunny.. both your expressions are kinda working and give same results. But the good maximum valuesin my graphs are different.

I've tried your expressions to retrieve the max values and the results are as follows:

Maximum value of First Expression given is 11985

Maximum value of Second Expression given is 19.94

However, according to my graphs for each country, the max value which i see visually on the graphs are:

For First Expression: value 8605

For Second Expression: value 27.6

Did i omit a condition in my max expressions?

[sunny_talwar]

Try using these formulas in the straight table and see what values you get for the total column:

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

2) Max(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]

Have you checked also the values not currently visible (using the scroll bar)?

[#8]

Hi i've tried the straight table and i get the results below:

[sunny_talwar · Accepted solution]

Try this in the text box object now (Added Country as the dimension like above)

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

Still need to figure out why the second one isn't working. Is 27.6 the max value from all the rows in the table below?

[#10]

Hi Swuehl yes ive checked the scrollbar and the max value i've mentionned is correct