topic Re: Counting month number of project Qlik Sense script in App Development

Counting month number of project Qlik Sense script

mfolmar — Tue, 01 Aug 2023 19:26:52 GMT

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

Re: Counting month number of project Qlik Sense script

cristianj23a — Tue, 01 Aug 2023 20:45:51 GMT

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;

Regarts.

Re: Counting month number of project Qlik Sense script

mfolmar — Wed, 02 Aug 2023 22:38:09 GMT

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?

Re: Counting month number of project Qlik Sense script

cristianj23a — Wed, 02 Aug 2023 22:52:55 GMT

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

Re: Counting month number of project Qlik Sense script

mfolmar — Thu, 03 Aug 2023 15:27:32 GMT

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".