Creating Range of Years

[ajith_qlik]

Hi,

I need to create a range of years i.e. from 2015,2016...2025 in a filter pane that can filter a report without using Inline something how we use between in SQL. I tried creating a variable and using makedate function and tried using that variable in a table that takes year values from 2015 to 2025 using Let vDate = makedate(2020,01,01) and in the column using vDate>=vDate-5 and vDate <vDate+5. Am I doing this incorrectly or is there any better approach, please let me know.

Regards

Ajith

[QlikTom]

A couple of questions:

1. Do you have access to the Data Load Script?
2. What is the name of the Date field you wish for the filter pane to associate with?

It is likely the solution best suited for creating a contiguous range of years, based on the min and max values of a date in your data is a master calendar table. 

https://community.qlik.com/t5/New-to-QlikView/How-to-Create-Master-Calendar/td-p/103109

[ajith_qlik]

Its a random generated years. The below is something which I need to associate with the Filter.

[Year$]:
Load * inline
[ Year

2019
2020
2021
2022
];

[QlikTom]

One method to generate a range of years is like so:

//Set Variables
SET vStartYear = 2019;
SET vNumOfYears = 6;

//Create Table with Range of years
MasterYears:
LOAD
	$(vStartYear) + IterNo() - 1 as [Year]
	AutoGenerate 1
WHILE $(vStartYear) + IterNo() - 1 <= $(vStartYear) + $(vNumOfYears); 

The result would be:

[ajith_qlik]

Thanks for the code. But this is not doing any change to the main table. And since its an independent table (of years to be used for filter) that has no association/relation to other tables, its creating a cartesian product in the main table. So for the below 3 records, its overall creating 9 records i.e. 3 each for 1, 2 and 3 ids as there are 3 statuses -  Sailed, StartYear&'Carried Forward Sail To'& End Year and StartYear&'New Sail'.  I want this year to used in a way such that when 2019 is clicked, it will not change the record count / filter the main table, it will just update the status of Shipment Comments column based on this year i.e. this year acts as a perspective to how the specific details of the shipment is perceived. Below is an example for the same.

For this I wanted to check how to create an association of the Year table with that with the main table from which the columns are being pulled to this report. Can you please help me in the same.

Many thanks in advance.

[QlikTom]

I misunderstood the original question as being along the lines of "how to generate a range of years". 

It looks like what you need a is a calculated dimension, where when the "Year" field is selected, if the year is greater than the [Destination Reach Date] year, the [Shipment Comments] field will change in some way. 

The code I gave will generate a range of years in a data island assuming the field is named uniquely in the data model.

I guess I would need a better explanation of the logic you want to implement. 

What is the logic that governs what the field changes to? Is it any value with "New Sail" in the Shipment Comments that changes to "Sailed" should the selected year be greater than the year of the Destination Reach Date?

One potential issue I see with that is if something has a [Destination Reach Date] value of 01/02/2020 it would not change to "Sailed" until 2021 is selected, even if the current date is 07/02/2020. That may or may not be an issue in your use case, but it would help me to get a handle on the logic you wish to implement.