Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! New to Qlik and scripting here, so bear with me!
Currently I have a few charts that have been hard coded to show certain data regardless of filters. I would like to edit the expressions so the values are variables so I won't have to update each year. An example of one expression is:
rangesum(above(Sum({1<STATUS_GROUP={'Closed'}, [V_QLIK_OPS_WORKTEAMS.WORK_TEAM_DEPT]={'MAINTENANCE'}, CFYear={'2016/2017'}>}COSTPO),0,12))
I currently followed the steps mentioned Here when it came to creating Master Calendars for each of my date fields, and ensuring they show the correct Fiscal Year (July 1 through June 30).
My question, which may be very simple, is how to create a variable for current Fiscal Year? I was able to create variables like the current calendar year "=Year(today())" and Previous Month "MonthName(Today(),-1), but how would I get it to configure so that I can create one that starts in July?
Thanks!
Do you have this in your data model
If you do, you might just be able to use this
Current Year
RangeSum(Above(Sum({1<STATUS_GROUP = {'Closed'}, [V_QLIK_OPS_WORKTEAMS.WORK_TEAM_DEPT] ={'MAINTENANCE'}, fYear = {"$(=Max(fYear))"}, CFYear>}COSTPO),0,12))
Previous Year
RangeSum(Above(Sum({1<STATUS_GROUP = {'Closed'}, [V_QLIK_OPS_WORKTEAMS.WORK_TEAM_DEPT] ={'MAINTENANCE'}, fYear = {"$(=Max(fYear)-1)"}, CFYear>}COSTPO),0,12))
Do you have this in your data model
If you do, you might just be able to use this
Current Year
RangeSum(Above(Sum({1<STATUS_GROUP = {'Closed'}, [V_QLIK_OPS_WORKTEAMS.WORK_TEAM_DEPT] ={'MAINTENANCE'}, fYear = {"$(=Max(fYear))"}, CFYear>}COSTPO),0,12))
Previous Year
RangeSum(Above(Sum({1<STATUS_GROUP = {'Closed'}, [V_QLIK_OPS_WORKTEAMS.WORK_TEAM_DEPT] ={'MAINTENANCE'}, fYear = {"$(=Max(fYear)-1)"}, CFYear>}COSTPO),0,12))
We had a Qlik consultant help us with our master calendar and we had them help with a bunch of flags. This one was used for our fiscal year. I updated it so that it matches your July1-jun30 year:
If( (year(Date)=year(Today()) and num(Month(Date))<=7) or
(Year(Date)=(year(Today())-1) and num(Month(Date))>6),1) as FYFlag,
then when you use it in a set analysis, ..., FYFlag = 1,...
Since it's in the master calendar, we reload the calendar daily. The current fiscal year will always update. Now, when July starts, there won't be much data, but that's how it goes.
I changed the fYear in each calendar I used, but that seems to be what I need to reference. Thanks!
This is also a good solution. Thanks!