Opening Event in Doc not working

[QFanatic]

hi Guys

PFA Scrambled model.

I want the model to execute the Select indicated, upon opening.

how the data works - Predicted values populate future values as well, so eg for budget I will have values for all Periods. For actuals obviously, that wont be the case (the flag field is called HasValue).

So when the doc opens, I want to go to the MAX Financial year, where values have been populated (HasValue = 1).

Its not working

Appreciate all assistance.

Thanks

[rwunderlich · Accepted solution]

Looking in your qvw the contents of your select variable is:

=(max({<PeriodID={"$(=max(PeriodID))"},HasValue={'1'}>}FY)) 

This is not working because the Max(PeriodID) does not HasValue=1. So you get nothing. 

You want the max(PeriodId) within the set of PeriodIds with HasValue=1.

=(only({<PeriodID={"$(=max({<HasValue={'1'}>}PeriodID))"}>}FY)) 

Note I also changed the first max() to only(). Your FY values are like "2020/2021" which are not numbers. 

Another required change is your OnOpen Select in Field value should not include $() around the variable. Should be:

=vOpenString 

With the $() the FY value will get evaluated as arithmetic giving you  2020 divided by 2021.

You could also code the expression as:

=(FirstSortedValue({<HasValue={'1'}>}FY, -PeriodID)) 

which strikes me as easier to read.

-Rob

[QFanatic]

Hi Rob,

Thank you very much for your assistance. Much appreciated.

[QFanatic]

Rob,

if I wanted to use your solution (FirstSortedValue({<HasValue={'1'}>}FY, -PeriodID))  in a expression on a chart, how would I do it?

Expression would look sometime like this...

sum({<Channel = {'Banking App'},CostName={'Prev_Yr_Actuals'},Period=, FY={"=$(YourSolution)"}>}Value)...

(The FY field is a dual field.)

in Short - I want the same behavior that we mimicked on Open Event, to happen if a user didnt make a selection on FY.

Thank you

(I have created a separate thread for this question - https://community.qlik.com/t5/QlikView-App-Dev/Syntax-using-FirstSortedValue-in-set-Analysis/m-p/179...)

Thanks