topic if statement, greater than and less than MAX in App Development

if statement, greater than and less than MAX

Meg00 — Sat, 16 Nov 2024 17:18:34 GMT

Hi everyone!
I am trying to create a variable. The result I'm trying to achieve is depending on selected period (YYYYMM) I want the variable to always show selected period-1 to selected period +11 (rolling 12 if you want).

I've created a PeriodCounter that looks correct:

Period	PeriodCounter
202101	1
202102	2
202103	3
202104	4

etc.

And then trying this syntax:

vPeriods=

if(PeriodCounter >= '$(=Max(PeriodCounter)-1)' or PeriodCounter < '$(=Max(PeriodCounter)+11)', Period)

and it looks correct:

so in this case 202104 is selected, I use the vPeriods as dimension in table, but it only shows 202104.

I have a feeling I've messed up the syntax somewhere.

Does anyone have a better option or know where I've done something wrong?

Re: if statement, greater than and less than MAX

Gysbert_Wassenaar — Wed, 07 Apr 2021 13:27:43 GMT

I'd suggest you solve this problem in the load script by creating an AsOf table.

See this article for example: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

Re: if statement, greater than and less than MAX

Dalton_Ruer — Wed, 07 Apr 2021 13:29:58 GMT

When you say that 202104 is SELECTED I take that to mean that you have physically selected it in a filter. In which case I would fully expect that it is the only that shows.

I think what you likely need is a DATE ISLAND so that you can select it from the island table which isn't associated to your other data. Then you should be able to accomplish what you are looking to do. Or you can utilize SET ANALYSIS to override the period selection. Not sure of your data model and use case.

Here is 1 example of a Master Calendar as a Data Island https://optimalbi.com/crafting-qlik-sense-calendars-part-3-of-4-date-islands/

Re: if statement, greater than and less than MAX

Meg00 — Wed, 07 Apr 2021 13:44:30 GMT

you're absolutely right, I still had the expression I made for current period in mind and thought I could work from that.
If( Date#(Period&'01', 'YYYYMMDD') >= MonthStart(AddMonths($(CurrentDate), - 1)) and Date#(Period&'01', 'YYYYMMDD') < MonthStart(AddMonths($(CurrentDate), 11)), Period)