Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Partner
Partner

Set analysis with Variable, expression fails using less than

Hi guys,

I have an app where our customer required the functionality of dynamic current year-base year selection. I am solving this by the following method:

when user selects one or more years, I am choosing the latest from them, this is saved into a variable (vTárgyév).

This variable is the result of this expression:

SubField(GetFieldSelections([OrderYear],',',GetSelectedCount([OrderYear])),',',GetSelectedCount([OrderYear]))

I have a chart, where I have to summarize all the values before the chosen date. For this, I am using set analysis with a less than, that looks like this:

sum({<[Account] = {38506}, OrderYear ={"<=$($(vTárgyév)-1)"}>} Tartozik-[Követel])

The result is the sum of all the data, not just the values of the years before. For me this means that Qlik could not evaluate the "OrderYear ={"<=$($(vTárgyév)-1)"}" part. 

I have already tried turning vTárgyév into a numerical type, but it was not successful. With just using "=" it works.

If I am just trying to get the current year, it works:

sum({<[Account] = {38506}, OrderYear ={"=$(vTárgyév)"}>} Tartozik-[Követel])

What is also very interesting that after this, if I change the expression (-1):

sum({<[Account] = {38506}, OrderYear ={"=$(vTárgyév)-1"}>} Tartozik-[Követel])

then it gives me the result of the expression with only =$(vTárgyév), so there is some problem evaluating the whole expression.

Do you have any idea how can I fix this?

Any help is much appreciated,

 

Thanks

 

Tibor

1 Solution

Accepted Solutions
MindaugasBacius
Valued Contributor III

Re: Set analysis with Variable, expression fails using less than

Have you tested:
sum({<[Account] = {38506}, OrderYear ={"<=$(=$(vTárgyév)-1)"}>} Tartozik-[Követel])
2 Replies
MindaugasBacius
Valued Contributor III

Re: Set analysis with Variable, expression fails using less than

Have you tested:
sum({<[Account] = {38506}, OrderYear ={"<=$(=$(vTárgyév)-1)"}>} Tartozik-[Követel])
Partner
Partner

Re: Set analysis with Variable, expression fails using less than

Hi Mindaugas,

yes, thank you! It worked, I've never tried putting the equal sign before the inner $ sign. 🙂

OrderYear ={"<=$(=$(vTárgyév)-1)"}

, but this was the solution.

Thanks again,

 

Tibor