2 Replies Latest reply: Feb 19, 2018 9:42 AM by Daphne Boateng RSS

    Created 'Fiscal Year' filter pane not filtering correctly

    Daphne Boateng

      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:

      Qlik Dates.PNG

      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