ideas about how to create a particular date filter

[P_qlik]

Hi,

I need to create a particular filter in nprinting:  the extraction should go from the last month of two FY preceding the month just ended (e.g. if it is February 2, 2021, the month year to be selected is from March 2019 to January 2021. If it is May 2, 2021, the month year to be selected is from March 2020 to April 2021. If it is April 2, 2022, the month / year to select is from March 2020 to March 2022).

FY is fiscal year and the end of the fiscal year is always march.

How can I do?

Thank you

[Lech_Miszkiewicz]

Hi,

This question has a lot of information missing and does not allow us to answer it in100%. I will try to guide the conversation so we can extract all the required info: 

• how is your data model built
  • Did you create flags in your data model for year start, year end 
  • Have you set variable to March being first month: SET FirstMonthOfYear=3;
  • Do you number month numbers (March  = 1, Apr = 2.. etc...)
  • For the purpose of defining the starting period we could use YearStart function with parameters:
    • https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...
  • what is the main field you want to filter
    • is it just one field like Month-Year? If it is then the next important question is how this field is created in QlikView load script? Is it for example simply: Date(Floor(MonthStart(some date)),'MMM-YY') or is it something else? What? - we MUST know this to give you correct answer!!!
      • or
    • do you wanted to filter separately fields Month and Year which will not make any sense and would be impossible to do!!!
      • or
    • maybe you actually have more granular data in form of DATE field which we can use for that filtering?
      • if that is the case you could just use your "Date" field filtering with advanced search. Again it will be important to know again how your date field is created in QlikView load script as this drives the way we need to apply filter.
      • If your date field is simply just date loaded as typical Date(Floor(SomeDate)) as DATE then your advanced filter would be:
        • =( [DATE] >= Floor(YearStart(AddMonths(Today(),-1),-1,3)) )*( [DATE] < Floor(MonthEnd(Today(),-1)) )
        • if your date is a timestamp you may need to consider "time portion" too, although above expression should work regardles
• Also is your financial year aligning with regular calendar months, meaning that each month is simply from 1st to last day of Month or is it more like retail calendar 4-4-5 or similar. 

Understanding exactly how your calendar is build and data types of the fields used is critical to provide you with the solution. The reasons behind it are due to the fact how Date & Time function work vs Text and Number function. There can be significant differences in the way how expression need to be built.

more about date filtering in my posts here:

• https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/
• https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/