topic Re: dynamic bookmark interferes with getfieldselections in table expression in App Development

dynamic bookmark interferes with getfieldselections in table expression

Crichter141 — Wed, 26 Jan 2022 15:20:44 GMT

QLIK SENSE ONLY Please.

Bear with me, I swear this is a bookmark question. I'm not trying to be confusing - I had a process that worked UNTIL the bookmark was applied. Then my set analysis stopped working because the bookmark changed my result variable.

1. Set Analysis for YTD: I have a set analysis that goes like this:

Sum({<
MONTHLY_DATE={">=$(vCurrentDateSelectionFYStart) <=$(vMaxCurrentDateSelection)"},
MAIN_FLAG={'1'},
ACCOUNT_TYPE={'P & L'},
LEVEL_4={'TOTAL OPERATING EXPENSES'},
MONTHLY_TRANSACTION_TYPE_GROUP-={'B','F'}
>}MONTHLY_AMOUNT)

2. Variable used in Set Analysis: The variable vCurrentDateSelectionFYStart is based on a field called MONTH_YEAR it's written like this:

LET vCurrentDateSelectionFYStart = '= if(GetSelectedCount([MONTH_YEAR])=1,NUM(MAKEDATE(IF(MONTH(Date#(GetFieldSelections([MONTH_YEAR]),''MMM-YYYY'')) >= 7, YEAR(Date#(GetFieldSelections([MONTH_YEAR]),''MMM-YYYY'')),
YEAR(Date#(GetFieldSelections([MONTH_YEAR]),''MMM-YYYY'')) - 1), 07, 01)))';

And it works fine. I highlighted the field above that's changing after the bookmark is applied.

3. Bookmark Applied: Then the user wanted to default the MONTH_YEAR to start as the last finished month (so for Jan 26,2022) it would be Dec-2021. Pretty straight forward: I applied a bookmark to the MONTH_YEAR field

{<MONTH_YEAR={"=MONTH_YEAR=MONTH($(var_1MonthAgo))&'-'&YEAR(var_1MonthAgo)"}>}

4. BookMark causing issue with the set analysis variable: Now the set analysis will not work because the MONTH_YEAR, piece of my variable is coming through as

=MONTH_YEAR=MONTH($(var_1MonthAgo))&'-'&YEAR(var_1MonthAgo) appears everywhere above where the MONTH_YEAR was bolded.

Can I do the default bookmark in a way that doesn't impact the set analysis (variable)?

Thanks guys, I'm stumped right now!

#Qlik Sense

Re: dynamic bookmark interferes with getfieldselections in table expression

gus12 — Tue, 04 Oct 2022 14:05:53 GMT

Hey! I am facing the exact same issue. Did you come up with a solution on your own?

Many thanks,

Gus

Re: dynamic bookmark interferes with getfieldselections in table expression

gus12 — Tue, 04 Oct 2022 15:40:24 GMT

Managed to find a workaround by myself, so far it seems to work. For other users who encounter this issue, what I did was that I defined a variable with =GetFieldSelections([DateDimension]) but with an if statement before, "trapping" the string that the default bookmark makes GetFieldSelections return, like this:

if(Left(GetFieldSelections([DateDimension]),1,='=',today(),GetFieldSelections([DateDimension]).

When the default bookmark is applied the first character of the GetFieldSelection([DateDimension]) is an '=' sign, hence the if statement.

The today function can easily be replaced by another variable or any function that returns a proper value and not a text string.

Maybe not the most beautiful solution, but it seems to work in my case.