Filter FX for Year Ranges based off of a Date

[Sonia277]

Ranges for Years in Position

Please help.. I have a Scatter chart: Gender Pay Gap

The dimension is [Gender]

The measures are [Compa Ratio] & Years in Position. The Years in Position is a date value in the database. So I have used the following

formula on fx: Avg(interval(today() - ([Date In Position]))/365)

The Data is displayed as in the screen shot

Now the filter I am trying to set requires a dimension. I would like the filter to show ranges for filtering Years in Position based off of the Avg(interval(today() - ([Date In Position]))/365) formula. but by 2 years for example Years 0-2, Years 2-4, Years 4-6 and so forth.

This is what I put in the filter dimension: 

Dual('Year ('&Replace(Class(interval(today() - (([Date In Position]))/365), 2),'<= x <','-')&')',Class(interval(today() - (([Date In Position]))/365),2)

But I am getting the following selection in the filter? What am I doing wrong?

[rogerpegler · Accepted solution]

The numbers like 43446 are dates - for example enter it inot Excel and format as a date.

To format it in Qlik use the Date() function eg Class(interval(today() - (([Date In Position]))/365) becomes

Date(Class(interval(today() - (([Date In Position]))/365)).  You can add an extra parameter if you want to overide the default format eg

Date(Class(interval(today() - (([Date In Position]))/365),'DD/MM/YY')

A couple of notes - performance of this will be poor as it has to evaluate today() every time there is an action such as filter. Instead create a variable in the loadscript and use that. Even better, calculate the years in the position in the load script.

[Sonia277]

thank you that worked!