How to show previos appearance before the last - with(out) selection ?

[yanivvl0]

hi to all , I summarize in the text box the units where year = 2016 and Category = 4, by clicking it i am activating a selection according to this 2 fields.  Once selected  i need to see the last status (by model_Proj_STATUS_NUMBER in descending order) - so far no problems, but when I want to see the status before him - WITHOUT consideration of year and Category I get nothing :

The Requested values below are highlighted in yellow:

you can use the attach QVW. 

THANKS!!

[maksim_senin]

Hi Yaniv,

There are two things:

1. If you don't need to consider the year just add an exclusion to set analysis. In your case:

{<model_STATUS_DATE_FROM_Year=>}

2. Some model_Proj's have only one status, so the sort function cannot return the second value. Rather it return NULL.

Best regards,

Maxim

[yanivvl0]

Hi Maxim , i try to do as you say:

                  FirstSortedValue (  {<model_STATUS_DATE_FROM_Year= , model_Category= >}  model_Proj_STATUS , -model_Proj_STATUS_NUMBER,2 )  

, but something is still missing , see 100631  case  , if we set the year to 2015 the last status = 60 , BUT the prev_last_status need to be the one BEFORE HIM -> 30 !    not 70 :

hope it is clear now , thanks .

[sunny_talwar]

May be this is what you need:

=FirstSortedValue({<model_Category = {"$(='<=' & Max(model_Category))"}, model_STATUS_DATE_FROM_Year = {"$(='<=' & Max(model_STATUS_DATE_FROM_Year))"}>}  model_Proj_STATUS , -model_Proj_STATUS_NUMBER, 2)

[yanivvl0]

hi Sunny , thanks , no it is steel no good , please see my answer to Maxim , if anybody need more explanation i will be glad to.

[sunny_talwar · Accepted solution]

Why won't you select a 4 for a previous year here and why would you go back to 2013?

[yanivvl0]

I understand that its look starge demand but what can i do , this is what the Boss want ..

Help

[sunny_talwar]

What is the logic behind selecting 30? Because it is from a category lower than 4 (selected model_Category)?

[yanivvl0]

OK , I explain the issue more fully: Once we found the request status (60) by year (2015) and by category (4), then we have to find the additional status and is - the status that preceded it ( order  descending by model_Proj_STATUS_NUMBER ) , but now, regardless of the year and category - simply the previos to (30) .

hope it is now Crystal clear.

[maksim_senin]

Hi Yaniv,

But 2015 is not selected at the screenshot. What do you mean by "finding if status by year"? None of your chart try to show this. What is the logic which can be used to define which year is in the scope?

Also you wrote you need to calc it regardless of the years. Given this, we have a correct result. Otherwise I missunderstood you.

Maybe it's possible to calc it at the load script level, but I cannot even imagine an algorithm now.

Best regards,

Maxim.