Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mfolmar
Contributor II
Contributor II

Counting month number of project Qlik Sense script

I’m looking for a way to count the month number of a long list of projects (start dates over many years) so that I may compare month 1, month 2, etc of each project to each other. Does anyone have a method I can try? 

Current Data:  

Project #

Project Start Date

Project End Date

ABC1

5/4/2021

10/10/2021

QWERTY2

1/1/2022

7/23/2022

XZY3

5/5/2023

8/1/2023

 

Desired Output:

Project #

Calendar Month

Month Number

ABC1

May 2021

1

ABC1

June 2021

2

ABC1

July 2021

3

ABC1

Aug 2021

4

ABC1

Sept 2021

5

ABC1

Oct 2021

6

QWERTY2

Jan 2022

1

QWERTY2

Feb 2022

2

QWERTY2

March 2022

3

QWERTY2

Apr 2022

4

QWERTY2

May 2022

5

QWERTY2

Jun 2022

6

QWERTY2

Jul 2022

7

XZY3

May 2023

1

XZY3

Jun 2023

2

XZY3

Jul 2023

3

XZY3

Aug 2023

4

Labels (1)
1 Solution

Accepted Solutions
cristianj23a
Partner - Creator III
Partner - Creator III

Hello, here I have what you need.
Try this code:

// First, we load the project data
Projects:
LOAD *,
Date#([Project Start Date], 'M/D/YYYY') as StartDate,
Date#([Project End Date], 'M/D/YYYY') as EndDate INLINE [
"Project #", "Project Start Date", "Project End Date"
ABC1, 5/04/2021, 10/10/2021
QWERTY2, 1/01/2022, 7/23/2022
XZY3, 5/05/2023, 8/01/2023
];

// Then, we generate the months for each project
ProjectMonths:
LOAD
[Project #],
Date(MonthStart(StartDate, IterNo() - 1), 'MMM-YY') as CalendarMonth,
IterNo() as MonthNumber
RESIDENT Projects
While MonthStart(StartDate, IterNo() - 1) <= EndDate;

cristianj23a_0-1690922740943.png

 

 

Regarts.

 

 

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.

View solution in original post

4 Replies
cristianj23a
Partner - Creator III
Partner - Creator III

Hello, here I have what you need.
Try this code:

// First, we load the project data
Projects:
LOAD *,
Date#([Project Start Date], 'M/D/YYYY') as StartDate,
Date#([Project End Date], 'M/D/YYYY') as EndDate INLINE [
"Project #", "Project Start Date", "Project End Date"
ABC1, 5/04/2021, 10/10/2021
QWERTY2, 1/01/2022, 7/23/2022
XZY3, 5/05/2023, 8/01/2023
];

// Then, we generate the months for each project
ProjectMonths:
LOAD
[Project #],
Date(MonthStart(StartDate, IterNo() - 1), 'MMM-YY') as CalendarMonth,
IterNo() as MonthNumber
RESIDENT Projects
While MonthStart(StartDate, IterNo() - 1) <= EndDate;

cristianj23a_0-1690922740943.png

 

 

Regarts.

 

 

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
mfolmar
Contributor II
Contributor II
Author

I thought this was the solution, however, I am now seeing an issue - CalendarMonth and MonthNumber are not associated to my original data, so I am unable to total the number of orders per month. 

Is there a way to retain that information with this script? 

cristianj23a
Partner - Creator III
Partner - Creator III

Hello, what fields do they have and what tables, to create an example.

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
mfolmar
Contributor II
Contributor II
Author

To create the original table "Current Data", I used a field called "Project Expenditure Date" to calculate both the "Project Start Date" and "Project End Date".