topic Re: automatically select last 10 quarters in chart on sheet opening in App Development

automatically select last 10 quarters in chart on sheet opening

tobiasaechter — Fri, 19 Apr 2024 08:53:09 GMT

Hi,

I have a bar chart showing sales volume per quarter (quarters on the x-axis)

currently the chart is fixed to always show the last 10 quarters, but ideally I would like to make it flexible, so that the users can select the quarters visible on their own, but when opening the sheet initially I would like to have the last 10 quarters selected automatically..

As the FYFQ field is a string like FY23-Q1, I additionally created a FYFQ_Row field which is a number count. Meaning 1 is the oldest FYFQ available and the highest number is the most recent FYFQ.

currently the chart is fixed to show always the last 10 quarters with the following dimension "if(FYFQ_Row > v_maxFYQ-10 and FYFQ_Row <= v_maxFYQ, FYFQ)" where "v_maxFYQ is defined as max(FYFQ_Row)

this works perfectly fine.

but my question is now the following:
when changing the dimension to "FYFQ" obviously the chart will show all quarters available.
I know that through Sheet Actions I can add actions that are performed on opening of the sheet, like select a specific field value, etc.

But is it possible to somehow make the selection from above dimension on sheet opening. Meaning can I automatically select the last 10 quarters in the FYFQ field on sheet opening?

Thanks for your help.

Re: automatically select last 10 quarters in chart on sheet opening

marcus_sommer — Fri, 19 Apr 2024 10:10:51 GMT

IMO each kind of action-magic raised more challenges as it would be solving. If any possible you should try to avoid such approaches.

Ideally the user selects always directly all the wanted data. This may in this case simply a search like:

<=10

against a continuous quarter-field - like yours only with the opposite direction and starting with 0 or 1 by the current one which would mostly avoid to query a max() value of it and if such logic is really needed it could be caught with min() in the opposite direction. A search like the >=10 might be also directly stored before closing the application or called with a bookmark.

Further possible would be to cluster such fields with class() or with conditional approaches like pick(match()) respectively if-loops, for example (with an appropriate running field of Q):

pick(match(-1, Q = 0, Q <= 10, ...), 'current', 'last 10', ...)

and by using specialized dimensions of The As-Of Table - Qlik Community - 1466130 you may combine multiple flags and/or cluster to provide the user all common period-requirements like YTD, MTD, LYTD, ... within a single selection.

Beside this I suggest to consider to move the condition from the (calculated) dimension into the expression which would enable approaches like:

sum({< Q *= {">0<=10 "}>} Sales)