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

[vinieme12]

Your MatPlantMMYY field should be number or a datevalue, so create a new YearMonth field using YearNo and MonthNo fields

YearNo&NUM(MonthNo,'##') as YearMonth

in the expression use

= firstsortedvalue(MAPeur,-YearMonth)

[vinieme12]

or Try this; but you need to get rid of the null values in those fields, for the year assign value like 1000 and month 0!

FirstSortedValue(MAPeur,-(YearNo&MonthNo))

[#4]

Thanks for your reply !

But still I am getting null ( - ) for some of the items whose M count is > 1. Also I am not able to understand your 1000 and 0 setting

But most of the M values are displayed now. PFB screenshot.

Regards,

J

[tresesco]

Hi Bond,

So your weapons can't help you here.

I your requirement clear enough to me. Could you please explain your expected output with logic, against your sample file?

[#6]

For the rows which are null ( - ) in the M first sorted are null because there are more than one M values (see M count for those null rows). So we have to tell QlikView which M value should display. The condition is latest M value and it cn be implemented by using FirstsortedValue with weightage as YearNo&MonthNo which will identify the latest M value . So now it identified the latest M value for most of the items , but still some of the items are null ( - )  (as highlighted yellow in my last screenshot). We need to display the M values for those too now.

-J

[Anonymous]

Try below one

Aggr(FirstSortedValue(MAPeur,-(YearNo&MonthNo)),MAPeur)

[#8]

No luck man

[tresesco]

Second parameter of firstsortedvalue() has to return a numeric value. But in your expression FirstSortedValue( MAPeur, -MatPlantMMMYY), MatPlantMMMYY  is a string. So it doesn't work.

[#10]

Yes accepted. Now i changed it to YearNo&MonthNo. That is why I am getting values for some of the rows (see the row above the highlighted row in my latest screenshot). I am still worried about rows like the highlighted ones. why the sort weight  '-YearNo&MonthNo' is not working for those ?

-J

[tresesco]

One more issue noticed - most of the cases there is no MAPeur value on latest date. Ex. KNED-> 4J6K1029P001

latest date is Nov2016, and there is no MAPeur. Hence, firstsortedvalue would not get anything to return even if you provide the date field as the second parameter.

Had there been data for latest date, you could probably even use something like:

FirstSortedValue( MAPeur, -Date#(Right(MatPlantMMMYY, 7),'MMMYY'))