"Qlik Script: Dynamic Month Labels - Looping Issue from December 2023 to January 2024"

[Ayden]

Summary:

I am working on a script that dynamically generates month labels based on the current date. The script should generate labels for the current month up to 12 months in the future. However, I'm experiencing a problem with the script: when it reaches December 2023, it does not correctly loop to January 2024.

My use case
script to address the use case of loading data from an Excel spreadsheet with multiple columns representing months and years in the format "MMM-YYYY CQ" (e.g., "JAN-2023 CQ"). The goal is to have the script loop through the months dynamically, starting from the current month and year, and extending through the next 11 months, spanning two years if necessary.

This script will automatically adjust the month and year labels according to the current date, ensuring that the correct month and year are displayed in the output. The script also handles the transition from one year to the next, making it suitable for use with data that spans multiple years.

Here is the script I'm working on:

// Set current and next year variables
Let vCurrentYear = Year(Today());
Let vNextYear = Year(Today())+1;

// Loop through 12 months to generate labels
For n = 0 to 11
// Calculate the month number and year for each iteration
Let vMonth = Month(AddMonths(Today(), $(n)));
Let vYear = Year(AddMonths(Today(), $(n)));

// Check if the month is January and the iteration is greater than 0, then increment the year
If($(vMonth) = 1 and $(n) > 0, Let vYear = $(vYear) + 1);

// Create the month label with the calculated month and year
Let vMonthLabel = '[' & MonthName(AddMonths(Today(), $(n))) & '-' & $(vYear) & ' CQ]';
Let vMonths$(n+1) = $(vMonthLabel);
Next n;

// Load statement for the data
Commits:
Crosstable ("Month-Year CQ", Value, 4)
Load
Text("Part Number") As "Part Number"
,Text("Ordering Plant") As "Deliver-To Plant"
,Text("Supply Plant") As "Supply Plant"
,Text("Source Code") As "Source Code"
,$(vMonths1)
,$(vMonths2)
,$(vMonths3)
,$(vMonths4)
,$(vMonths5)
,$(vMonths6)
,$(vMonths7)
,$(vMonths8)
,$(vMonths9)
,$(vMonths10)
,$(vMonths11)
,$(vMonths12)
From [lib://ABC/XYZ/AAAACurrent Commits.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Calculate Total Bads Commit
TotalBadsCommit:
Load
"Part Number",
"Deliver-To Plant",
"Supply Plant",
"Source Code",
"Month-Year CQ",
Sum(Value) as "Total Bads Commit"
Resident Commits
Group By "Part Number", "Deliver-To Plant", "Supply Plant", "Source Code","Month-Year CQ";
drop table Commits;

[stevedark · Accepted solution]

Hi @Ayden 

You can do away with all the bits where you are trying to work out what year it is and use the Date function instead of the Month function on this line:

Let vMonthLabel = '[' & MonthName(AddMonths(Today(), $(n))) & '-' & $(vYear) & ' CQ]';

Replace it with teh following:

Let vMonthLabel = '[' & Date(AddMonths(Today(), n), 'MMM-YYYY') & ' CQ]';

It seems that there could also be a bug with the line:

Let vMonths$(n+1) = $(vMonthLabel);

This is because n+1  is not a variable.

What I would suggest is changing the for loop to be 1 to 12 instead of 0 to 11.

The broken line above can then be replaced with:

Let vMonths$(n) = $(vMonthLabel);

And you will have to move the add months function back a month by taking off the extra 1:

Let vMonthLabel = '[' & Date(AddMonths(Today(), n-1), 'MMM-YYYY') & ' CQ]';

I've not put that into Qlik to test it, but I believe it should give you what you need.

Kind regards,

Steve

https://www.quickintelligence.co.uk/blog/