conditional result for the expression in nested if

[#1]

Hello all !

In one of my table I have a column to display the price which comes in variable 'M' from a QVD. My dimension is the item name and the corresponding price (Here 'M') should be shown. I have to show the latest price if more than one price is available in the QVD. The column to identify the latest price is 'MonthYear'. Please help me in modifying the expression so that it will display the latest price.

So here we have to modify the expression part of first if , that is Max (M) . It should be Latest(M) according to MonthYear column.

If(Count(DISTINCT(M))>1,Max(M),if(Count(DISTINCT(M))=0,0,if(Count(DISTINCT(M))=1,M)))

-J

[#2]

Yes ! you are correct. In that case , Can we display the latest cum available value ? If so how we can modify the expression FirstSortedValue(MAPeur,-(YearNo&MonthNo)) to display it ?

-J

[tresesco]

Try like:

FirstSortedValue({<MAPeur={'*'}>} MAPeur , -Date#(Right(trim(MatPlantMMMYY), 7),'MMMYY'))

[#4]

This code works well in the sample report which i attached here, but it is not working in my actual report. I am still wondering where the gap is ! Anyway  I will take this as one solution !

BTW are you trimming MatPlantMMMYY by this ? -Date#(Right(trim(MatPlantMMMYY), 7),'MMMYY'))

[tresesco]

If so, please close this thread by marking correct/helpful answers.

[#6]

OK Sure ! But clarify this.

BTW are you trimming MatPlantMMMYY by this ? -Date#(Right(trim(MatPlantMMMYY), 7),'MMMYY'))

Because length of MatPlant in the MatPlantMMMYY will vary , I think this is the reason  I still get null for some of the items. please clarify

[tresesco]

One correction in date parsing format,  try like:

FirstSortedValue({<MAPeur={'*'}>} MAPeur , -Date#(Right(trim(MatPlantMMMYY), 7),'MMMYYYY'))

[#8]

Acceptable solution ! (Y)

[tresesco]

It's really hard to be appreciated by Bond. Really tough character. Great that finally I have been able to provide an acceptable solution.

[#10]

Yes ! You got it right !