Created 'Fiscal Year' filter pane not filtering correctly

Anonymous — Sat, 10 Feb 2018 09:57:05 GMT

Hi Everyone,

Hoping someone can shed some light on a problem I've been trying to figure out for over a week. I am using Qlik Sense through my company sourcing portal through the web (so it has all types of permissions attached to it, I can get most features but not all). I have created a date field which I wanted to run from Feb-Jan and that worked perfectly. The issue I'm having is that in order to create the date option correctly there are two fields that are needed. One is called 'project_financials_effective_start_date' and the other is 'project_benefit_tracker_number'. To start off with, none of these dates are stored as dates (they are stored as a string because of our sourcing portal) so we first have to convert both text strings to dates. The 'project_benefit_tracker_number' relates to a number of months after the 'project_financials_effective_start_date' so for example if the latter date was 01/02/2016 (DD/MM/YYYY) and the former number was 24 [this relates to the number of months] then the final output date would be 01/02/2018. I have also stated in the formula that I don't want to show an dates before 01/02/2015. Below is the formula in my fiscal year calculation:

if(YearName(Date(AddMonths(Date(project_financials_savings_effective_date, 'YYYY'), project_benefit_tracker_number), 'YYYY'),0,2) >= '01/02/2015', YearName(Date(AddMonths(Date(project_financials_savings_effective_date, 'YYYY'), project_benefit_tracker_number), 'YYYY'),0,2), null())

This formula works and returns values like I'd expect, but then when I actually try to filter on it, it doesn't exclude the other years, for example if I filter on '2017-2018' it still shows all the values from '2015-2016', '2018-2019' so on and so forth. As can be seen below:

I was thinking it might be because I am calling two fields in my formula, but both of these fields are needed in order to get the correct date - what am I missing?

Thanks in advance

Re: Created 'Fiscal Year' filter pane not filtering correctly

sunny_talwar — Fri, 16 Feb 2018 16:27:16 GMT

Do you use this filter using an expression on the front end of the app? I would suggest to create a new field using the above expression in the script.

Re: Created 'Fiscal Year' filter pane not filtering correctly

Anonymous — Mon, 19 Feb 2018 14:42:42 GMT

Thanks for your response. I am using the front end. Unfortunately I don't have access to the script as I am using Qlik through another web based application, this is reason I am creating the filter like this in the first place.

Any other suggestions?

topic Re: Created 'Fiscal Year' filter pane not filtering correctly in App Development

Created 'Fiscal Year' filter pane not filtering correctly

Re: Created 'Fiscal Year' filter pane not filtering correctly

Re: Created 'Fiscal Year' filter pane not filtering correctly