Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?