How to add a Fiscal Year

[kunkumnaveen]

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

[rodjager]

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;

[Brett_Bleess]

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

[Kushal_Chawda]

@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;