topic Re: How to add a Fiscal Year in QlikView

How to add a Fiscal Year

kunkumnaveen — Sat, 16 Nov 2024 00:04:07 GMT

Hello All,

Below is the list of the columns in a table ,where valid from and valid to are the calendar date.

Plant Supplier Part ValidFrom ValidTo Price

HP Jolt Screw 05-01-2018 25-07-2021 100

I am trying to add a column Fiscal year in above table which has to be derived from valid from and valid to columns ,as shown below

Plant Supplier Part ValidFrom ValidTo Price FiscalYear

HP Jolt Screw 05-01-2018 25-07-2021 100 2018

HP Jolt Screw 05-01-2018 25-07-2021 100 2019

HP Jolt Screw 05-01-2018 25-07-2021 100 2020

HP Jolt Screw 05-01-2018 25-07-2021 100 2021

HP Jolt Screw 05-01-2018 25-07-2021 100 2022

as the Valid from of a price for the specified plant,supplier,part is from 05-01-2018 and valid to 25-07-2021 ,which means the price is valid for more than 4 calender Yrs in term of Fiscal year it will be more than 5 yrs.

so i need to derive a column name Fiscal Year from valid from and Valid to for a plant,supplier,part combination

How to achieve this at backend scripting

Thanks

Naveen

Re: How to add a Fiscal Year

rodjager — Mon, 31 Aug 2020 23:30:12 GMT

Hi Naveen,

The first thought I had was to run some for loops over the data.

I know there is going to be a better (more efficient) way of doing this, probably using IntervalMatch or something like that, however, I suspect this will do what you want too.

Hope this helps.

Rod

// Load in the data [Data]: Load * Inline[ Plant, Supplier, Part, ValidFrom, ValidTo, Price HP, Jolt, Screw, 05-01-2018, 25-07-2021, 100 ]; // Loop through and add the FiscalYear field to the original data set Let vParts = FieldValueCount('Part'); For i = 1 to $(vParts) vStart = Peek('ValidFrom', $(i)-1, 'Data'); vEnd = Peek('ValidTo', $(i)-1, 'Data'); Let vYears = Year(Date#(vEnd, 'DD-MM-YYYY')) - Year(Date#(vStart, 'DD-MM-YYYY')); For j = 0 to $(vYears) [Final data]: Load Year(Date#('$(vStart)', 'DD-MM-YYYY')) + $(j) As FiscalYear, * Resident Data; Next j Next i // Tidy up by removing definitions table Drop Table Data;

Re: How to add a Fiscal Year

Brett_Bleess — Tue, 08 Sep 2020 20:55:48 GMT

Here is a Design Blog post on Fiscal Year calendar that may be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

Regards,
Brett

Re: How to add a Fiscal Year

Kushal_Chawda — Tue, 08 Sep 2020 22:56:58 GMT

@kunkumnaveen try below

[Data]: LOAD *, FiscalYearStart+IterNo()-1 as FiscalYear While FiscalYearStart+IterNo()-1<=FiscalYearEnd; LOAD *, year(YearStart(ValidFrom,0,4))+1 as FiscalYearStart, year(YearStart(ValidTo,0,4))+1 as FiscalYearEnd; Load * Inline[ Plant, Supplier, Part, ValidFrom, ValidTo, Price HP, Jolt, Screw, 05-01-2018, 25-07-2021, 100 ]; DROP Fields FiscalYearStart,FiscalYearEnd;