Using a variable containing a function in the element list of a set analysis

[DutchHans]

Hi All,

I have a straight table (chart) with one dimension as branches of industry.
The expressions count the number of customers in that branche that have used a certain feature from our software in weeknumber X. I have three expressions; where X is the previous week, the current week and the next week.

I used three variables for these weeks in the notation YYYYWW where YYYY is the year (e.g. 2021) and WW is the week number (e.g. 18). So for week 18 of 2021 the outcome is 202118 (a integer number).

The variable for the current week is set up as: Year(Today())*100+Week(Today())
I used the variable name vThisYearWeek.
Evaluating this variable in a text box wityh the statement =$(vVariableName) results in the correct number 202118.

My expression formula in the table is:
Count(DISTINCT {1<PlanningId={"*"},PlanningStatusId={2,3},YearWeek={"=$(vThisYearWeek)"}>}OgId)

YearWeek is a field in the data table.

But in stead of resulting in the count for this week, it results in a count as if the YearWeek is not filtered. As if the expression has been: Count(DISTINCT {1<PlanningId={"*"},PlanningStatusId={2,3}>}OgId)

When I subsitute the variable by the constant value 202118, then I get the correct results:
Count(DISTINCT {1<PlanningId={"*"},PlanningStatusId={2,3},YearWeek={202118}>}OgId)
So something in the evaluation for YearWeek makes it invalid or obsolete when the variable is used?

I have search all over the forum and tried different approaches but to no avail.
When using constants the script of my dashboard must be edited every week so that is no option.

I hope someone can help.

Thanks,
Hans

[rwunderlich]

Looking at your expression, it looks like it should work, but there is some extra stuff in there. 

You said "YearWeek={202118}" works.  The equivalent using a variable is:

YearWeek={$(vThisYearWeek)}

You don't need the equal sign or the double quotes. 

In the expression editor, take a look at your expression as expanded at the bottom of the editor screen. Does it look like it's substituting 202118 correctly?

-Rob

[DutchHans]

Hello Rob,
Thanks for your reply. I just tried it but the expression evaluates to null
now.
I have used =$(vThisYearWeek) in the label and that evaluates fine to
202118.
Maybe the comparison to YearWeek fails.
Can it be that $(vThisYearWeek) evaluates to a non numeric type?

-Hans

[rwunderlich]

What does the expanded expression in the editor look like?

[Vegar]

As the variable is a  expression you might need to add an equal sign in the parenthesis,  like this:

yearWeek={"$(=vThisYearWeek)"}

[DutchHans]

Hello Rob,

I think my version of Qlikview is too old to see this expanded expression.

I'm using November 2017 SR2. I will first install the latest version. Thanks for your help.

Hans

[DutchHans]

Hello Vegar,

I just tried this too, but this doesn't work either.

Hans

[rwunderlich]

The expanded expression is a Qlik Sense feature. You posted this in a Qlik Sense group, so I assumed QS.

-Rob

[DutchHans]

My fault Rob.

I'm using Qlikview and posted it in the wrong group.
But the two should be compatible I heard.

Anyway, I used the same variable expansion in the label field and there it expands to the correct value.
It only does not work in set analysis.

-Hans

[marcus_sommer]

In QlikView you could get such evaluation of the variable-expansion if you removed the label from the expression and looked then there by hoovering with the mouse and/or dragging the column-width and/or enabling multi-line headers to see the whole expression.

The suggestions from Rob and Vegar should have worked and if not I wouldn't be surprised that there are any comments within the variable which could easily lead to such issues. Recommended is to have no comments within the vraible itself.

- Marcus