Max date wise price and name

[kunkumnaveen]

Hello All,

              I am trying to show Each persons latest price based up on calendar dates ,so to achieve requirement I had return the below expression

                        avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>}Sprice_Price)

but for some reason it is randomly picking only one max date which is 01-10-2017 and showing only those name and price ,and the rest of name it is considering under null, what I mean is

It is showing only those name and price whose max date is 01-10-2017

Consider under null for below names whose max date is 31-03-2017

I am trying to show in a straight table

                                              Dimension: 1.Name

                                                                  2)Sprice_Calday

                                                 measure :

                                                            1)  avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>}Sprice_Price)

What am I missing in my expression

Thanks

[kunkumnaveen]

Any more suggestion on my requirement ...

PFA sample data file

[sunny_talwar]

May be this

Dimension

Name

SPrice_Calday

Expression

=Only(Aggr(If(SPrice_Calday = Max(TOTAL <Name>SPrice_Calday), Avg(Sprice_Price)), Name, SPrice_Calday))

[kunkumnaveen]

I thought what ever the expression written in table chart will work in bar chart too, the thing is I have used the below expression in bar chart which has a drill down dimension

                                                                Drill Down Dimension  :4_Name------->5_Name

Expression:

                Only(Aggr(If(SPrice_Calday = Max(TOTAL <4_Name>SPrice_Calday), Avg(Sprice_Price)), 4_Name, SPrice_Calday))

OutPut:

So at global level (Before Drill Down to 5_Name) the above expression giving out average price for each 4_Name value dimension which is correct (As below)

For LARGE:1301.17 is Avg of  (1272.54+1290.04+1340.92)/3

when I click on Large it will drill down to  further  level ( 5_Name),at this level I will have SINGLE,FLAT values

for SINGLE price has to be=(1272.54+1290.04)/2 and

     FLAT price has to be=(1340.92)/1

But what I am getting is the price is not splitting up when u drill down to next level as shown below

I even tried below expression:

Only(Aggr(If(SPrice_Calday = Max(TOTAL <4_Name,5_Name>SPrice_Calday), Avg(Sprice_Price)),4_Name,5_Name,SPrice_Calday))

From Above expression ,at global level(4_Name) I am getting null values when I drill down down I am getting the split  as below

Level1:

             Level2:

                                        

How to make both the levels to work with one expression.?

[sunny_talwar]

Would it be possible to share an update Excel file where we have those fields to test this out