13 Replies Latest reply: Nov 23, 2017 6:03 PM by Sunny Talwar

# Max date wise price and name

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

• ###### Re: Max date wise price and name

What are you getting when you use max(SPrice_Calday) in text box?

• ###### Re: Max date wise price and name

I am getting  01-10-2017 in KPI

• ###### Re: Max date wise price and name

Try this?

Avg({<SCOPE={'YES'},SPrice_Calday={'\$(=max(SPrice_Calday))'}>} TOTAL <Name> Sprice_Price)

OR

Aggr(Avg({<SCOPE={'YES'},SPrice_Calday={'\$(=max(SPrice_Calday))'}>} Sprice_Price), SPrice_Calday, Name)

• ###### Re: Max date wise price and name

I tried both ur expressions, still it is showing 01-10-2017 date price

• ###### Re: Max date wise price and name

Hi, i  am attaching sample data ,which might give a clear picture of my requirement

• ###### Re: Max date wise price and name

Hi Naveen,

Please use the below expression

=Only(If(Aggr(NODISTINCT MAX(SPrice_Calday), Name) = SPrice_Calday, avg({<SCOPE={'YES'}>}Sprice_Price)))

Many Thanks

Karthik

• ###### Re: Max date wise price and name

I am getting null values ,when I use above expression

• ###### Re: Max date wise price and name

Any more suggestion on my requirement ...

PFA sample data file

• ###### Re: Max date wise price and name

May be this

Dimension

Name

SPrice_Calday

Expression

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

• ###### Re: Max date wise price and name

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.?

• ###### Re: Max date wise price and name

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