topic Re: Opening Event in Doc not working in QlikView

Opening Event in Doc not working

QFanatic — Mon, 29 Mar 2021 18:25:56 GMT

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

Re: Opening Event in Doc not working

rwunderlich — Tue, 30 Mar 2021 05:07:16 GMT

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

Re: Opening Event in Doc not working

QFanatic — Tue, 30 Mar 2021 05:58:29 GMT

Hi Rob,

Thank you very much for your assistance. Much appreciated.

Re: Opening Event in Doc not working

QFanatic — Tue, 30 Mar 2021 10:33:21 GMT

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/1795617#M1211177)

Thanks