How to get max date

[kunkumnaveen]

Hello All,

In  a line chart I have two Dimensions:

                                                                  1)Fiscal Year

                                                                  2)PROD_HIER4_Name

I need to write expression where it has to pick  MAX--VALIDFROM date's ---->Average PRICE_MAT at PROD_HIER4_Name level

in the above case  it has to be       (2208.27+1722.9)/2 irrespective of BMATERIAL

I Tired the below expression but I am not getting any output...

aggr(avg({<VALIDFROM={'$(=max(PRICE_MAT))'}>}PRICE_MAT),PROD_HIER4_Name)

what am I missing in above expression

Thanks

[shraddha_g]

Use expression given by sunny as it is

[sunny_talwar]

Set analysis is evaluated once per chart. In your case you are looking for Max(VALIDFROM) per FISCAL YEAR. Set analysis won't be able to do this and you will need to use Aggr() function

[kunkumnaveen]

I Am not getting any value for the above expression...

I did but no out put

[sunny_talwar]

How about this

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR]> VALIDFROM), Sum({<[FISCAL YEAR]>} PRICE_MAT)), [FISCAL YEAR], VALIDFROM))

[kunkumnaveen]

Sunny ,I almost achieve my requirement ,only thing I had change is instead of SUM I used AVG ....

one issues even though my chart has two dimension

Dimension:

                                                                  1)Fiscal Year

                                                                  2)PROD_HIER4_Name

if I don't selected any value from  PROD_HIER4_Name then it is picking up that fiscal years max date price irrespective of PROD_HIER4_Name,

if I selected any values from PROD_HIER4_Name(Lets say Arjun) then I am getting Arjun Avg values per Fy

so how can I make same thing to display even before selection ,

I tried like this .....

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR]> VALIDFROM), avg({<[FISCAL YEAR]>} PRICE_MAT)),[FISCAL YEAR],VALIDFROM,PROD_HIER4_Name))

but no use....I am getting same

Before Selecting Arjun

After Selecting Arjun

[sunny_talwar · Accepted solution]

May be this

Avg({<[FISCAL YEAR]>} Aggr(If(Only({<[FISCAL YEAR]>} VALIDFROM) = Max({<[FISCAL YEAR]>} TOTAL <[FISCAL YEAR], PROD_HIER4_Name> VALIDFROM), Avg({<[FISCAL YEAR]>} PRICE_MAT)),[FISCAL YEAR],VALIDFROM,PROD_HIER4_Name))