Modifying a field by a variable in set analysis

[qlik_hunter]

Hi,

I am trying to use a variable in a chart set analysis expression to count the number of entries in the table that are a specified number of years away from the current one. The data looks like:

    ID, MainYear,   SecondaryYear
    '1', '2020', '2017'
    '1', '2020', '2018'
    '1', '2020', '2018'
    '1', '2020', '2018'
    '1', '2020', '2020'
    '1', '2020', '2020'
    '1', '2020', '2021'
    '1', '2020', '2023'

The variable is a user selectable input field, v_Offset.

The set analysis statement looks like

count(distinct {<[MainYear]={"=$([SecondaryYear]-$(v_Offset))"}>} ID)

I feel like I've tried every combination of =, $( ), etc. but nothing has worked as expected so far.

[BrunPierre]

Try.

=Count(DISTINCT {< [MainYear] = {"=$(=[SecondaryYear] - $(v_Offset))"} >} ID)

[marcus_sommer]

I'm not sure if it's really a syntactically matter else a logical issue. Wich value(s) do you expect from:

{"=$([SecondaryYear]-$(v_Offset))"}

in regard to which selection and data?

[qlik_hunter]

Thank you BrunPierre for the suggestion. It still does not work as I intended however.

I was hoping to select only the data having a SecondaryYear that is different from the MainYear by a user selectable offset. Essentially I am allowing the user to have data points consider records that are a specified number of years away.

There may be a better way to organize the data or arrive at the solution - do you have any suggestions?

[marcus_sommer]

I think it's still not clear which years should be included by:

"... SecondaryYear that is different from the MainYear by a user selectable offset ..."

Have the main- and secondary year a real relationship within the data or should be just used to intersect some years for the wanted selection? What selects the user main- and/or secondary year and/or the offset-variable and which years remain and which are excluded?

[qlik_hunter]

The Offset variable is a text field on the app so that users can input any number they wish.

The SecondaryYear is used as a way of associating entries, perhaps not in the best way. Basically I joined the table back on itself so that all entries with a common attribute are included. I did this because on the Qlik Distribution Plot, I am actually plotting the MainYear as the X-axis measure. I then let the user adjust the X value through the input field, but when dots overlap I need to have them color differently. This was my solution to allow the MainYear dots to "see" the SecondaryYear entries that would be on the same row.

Does this help explain it? Admittedly it is probably not the best data structure.

[marcus_sommer]

I must admit that I have some doubts - about your join-approach as well as using a year as axis for as distributing chart and also why a variable is used to change this year instead of just selecting another one if it's wanted?

[QlikKing99]

I would agree with @marcus_sommer about the questionable join approach, but if you must use a variable as a set modifier, you can use a dollar sign expansion to do so. Something like:

count(distinct {<[MainYear]={"=$([SecondaryYear]-$(#v_Offset))"}>} ID)

There should be a page with a little bit more in the documentation: set-modifiers-dollar-sign-expansions 

In fact, might be good reading over the set analysis page a bit more. Happy Qlik'ing!