Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use expression given by sunny as it is
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
I Am not getting any value for the above expression...
I did but no out put
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))
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
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))