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]

VALIDFROM ={"$(=max(VALIDFROM))"}

[kunkumnaveen]

Hi, Could u say what am I missing in second chart

Chart1) avg({<[FISCAL YEAR]=>}PRICE_MAT)

Chart 2)  avg({<VALIDFROM ={"$(=max(VALIDFROM))"},[FISCAL YEAR]= >}PRICE_MAT)

In second chart I am getting only selected fiscal year ,even though I disable the fiscal year selection

what am I missing?

[shraddha_g]

It will only give you data for max validfrom date.

What is the max(VALIDFROM) returnig?

[sunny_talwar]

Because your ValidFrom is a date and it might only show up in the most recent year...

[shraddha_g]

What is the dimension you have used in chart?

[kunkumnaveen]

For Each fiscal year I need to show Max Valid form's Average price

[kunkumnaveen]

Dimension:

                                                                  1)Fiscal Year

                                                                  2)PROD_HIER4_Name

[sunny_talwar]

Then may be try this

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

[kunkumnaveen]

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

Can u help me , what should I need to do in the below expression to display all the FY data irrespective   of selection

avg({<VALIDFROM ={"$(=max(VALIDFROM))"},[FISCAL YEAR]= >}PRICE_MAT)