Calculated fields in Script load / table columns by month

[Sara_3]

Hi everybody!

I'm new to Qlik and  I stumbled upon a problem in script load. I need to calculate 4  fields and show them in the table columns. please help me to show them in table by Month like Jan 2023 , Feb 2023, and so on.

I have data from Jan 2019 to present but here i am created inline sample table.

I have logic for these calculations and they are static for Jan 2023. it worked fine in KPI's but I don't able to show them in the table dynamically for every month. The logic of the calculations should be like  for Jan 2023 :

Calculation 1 :

P Release Date is before start of month 

Deleted On is blank

PO Release Date is blank

Calculation in KPI: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={-},[PO Release Date]={-}>}[P Release Date])

Calculation 2 :

P Release Date is before start of month 

Deleted On is blank

PO Release date is after start of month 

Calculation in KPI: Count({$<[P Release Date]={"<=12/31/2022"}, [Deleted On ]={-},[PO  Release Date]={">=1/2/2023"}>}[P Release Date])

Calculation 3 :

P Release Date is before start of month

Deleted On is after start of month

PO Release Date is blank

Calculation in KPI: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO Release Date]={"-"}>}[P Release Date])

Calculation 4 :

P Release Date is before start of month

Deleted On is after start of month

PO Release Date is after start of month

Calculation in KPI: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO  Release Date]={">=1/1/2023"}>}[P Release Date])

Calculation 5 :

Add 1, 2, 3, and 4.

Let's have an example:

main:
load * inline [
P Release Date, Deleted On, PO Release Date
12/21/2022, -, 1/18/2023
12/23/2022, 2/27/2023, -
12/27/2022, -, -
1/4/2023, 1/24/2023, -
1/4/2023, -, 1/17/2023
1/4/2023, -, 2/28/2023
1/4/2023, -, 11/30/2023
1/6/2023, -, 2/9/2023
1/6/2023, -, 2/16/2024
1/11/2023, 5/10/2023, -
1/17/2023, 3/17/2023, -
1/17/2023, -, 4/11/2023
2/2/2023, -, 2/7/2023
2/2/2023, -, 6/21/2023
2/6/2023, 6/6/2023, -
2/15/2023, 9/11/2023, -
2/15/2023, -, 3/14/2024
3/2/2023, -, 5/3/2023
3/2/2023, -, 8/16/2023
3/2/2023, -, 9/22/2023
3/6/2023, -, 1/2/2024
3/30/2023, 4/11/2023, -
3/30/2023, 6/7/2023, -
3/30/2023, 8/7/2023, -
3/30/2023, -, 4/20/2023
3/30/2023, -, 8/29/2023
];

Output Result (in the table):

	Calculation 1	Calculation 2	Calculation 3	Calculation 4	Total of calculation 1,2,3, and 4
Jan 2023	0	1	1	0	2
Feb 2023	1	4	3	0	8
Mar 2023	1	3	4	0	8

Do you have any ideas, please?

[diegozecchini]

Hi!
To achieve this in Qlik, you need to dynamically calculate your required fields in the script and display them in a pivot table or straight table, grouped by months.

First of all, ensure your date fields (P Release Date, Deleted On, and PO Release Date) are properly recognized as dates.

main:
LOAD
Date(Date#([P Release Date], 'MM/DD/YYYY')) as [P Release Date],
Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Deleted On],
Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date]
INLINE [
P Release Date, Deleted On, PO Release Date
12/21/2022, -, 1/18/2023
12/23/2022, 2/27/2023, -
...
];
Replace ... with the rest of your data.

Then create a master calendar to ensure that you can calculate data by month.

Calendar:
LOAD
Date(MonthStart(AddMonths(Min([P Release Date]), IterNo()-1))) as MonthStart
AUTOGENERATE 1
WHILE MonthStart(AddMonths(Min([P Release Date]), IterNo()-1)) <= MonthStart(Max([P Release Date]));

LEFT JOIN (Calendar)
LOAD
MonthStart(Date#(MonthStart, 'YYYY-MM-DD')) as MonthStart,
MonthName(MonthStart(Date#(MonthStart, 'YYYY-MM-DD'))) as MonthName
RESIDENT Calendar;

Finally create calculated fields for each of your conditions.

LEFT JOIN (main)
LOAD
MonthStart([P Release Date]) as MonthStart,
// Calculation 1
If([P Release Date] <= MonthStart, If(IsNull([Deleted On]) and IsNull([PO Release Date]), 1, 0)) as Calc1,
// Calculation 2
If([P Release Date] <= MonthStart and IsNull([Deleted On]) and [PO Release Date] >= MonthStart, 1, 0) as Calc2,
// Calculation 3
If([P Release Date] <= MonthStart and [Deleted On] >= MonthStart and IsNull([PO Release Date]), 1, 0) as Calc3,
// Calculation 4
If([P Release Date] <= MonthStart and [Deleted On] >= MonthStart and [PO Release Date] >= MonthStart, 1, 0) as Calc4
RESIDENT main;

Now you can group your data by MonthStart to get monthly totals for each calculation.

AggregatedData:
LOAD
MonthName(MonthStart) as Month,
Sum(Calc1) as [Calculation 1],
Sum(Calc2) as [Calculation 2],
Sum(Calc3) as [Calculation 3],
Sum(Calc4) as [Calculation 4],
Sum(Calc1) + Sum(Calc2) + Sum(Calc3) + Sum(Calc4) as Total
RESIDENT main
GROUP BY MonthStart;

Now that you have the AggregatedData table, you can create a table in Qlik with the following columns:

Month
Calculation 1
Calculation 2
Calculation 3
Calculation 4
Total

could it works?
kind regards,
Diego

[Sara_3]

Thank you so much , I am trying and let you know.

[Sara_3]

when I load the script,I got an error (Aggregation function not allowed here) when I ran the following script :

Calendar:
LOAD
Date(MonthStart(AddMonths(Min([P Release Date]), IterNo()-1))) as MonthStart
AUTOGENERATE 1
WHILE MonthStart(AddMonths(Min([P Release Date]), IterNo()-1)) <= MonthStart(Max([P Release Date]));

I think because we are using Min and Max without Group by clause??????

[diegozecchini]

Hi!
To address this issue, I think we shourd calculate the min and max dates in a preceding step and store them in variables. Then use these variables in the calendar generation.

// Calculate Min and Max Dates
LET vMinDate = Num(Peek('MinDate', 0, 'DateBounds'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'DateBounds'));

DateBounds:
LOAD
Min([P Release Date]) as MinDate,
Max([P Release Date]) as MaxDate
RESIDENT main;

// Generate Calendar using the variables
Calendar:
LOAD
Date(MonthStart($(vMinDate), IterNo() - 1)) as MonthStart
AUTOGENERATE 1
WHILE MonthStart($(vMinDate), IterNo() - 1) <= MonthStart($(vMaxDate));

This avoids the need for aggregation functions directly in the WHILE loop.
Once the calendar is generated, the remaining script for creating calculated fields and aggregating data by month remains the same.

Let me know if this resolves your issue!

[Sara_3]

 Hi Diego, 

Thanks you so much for your response, I really appreciated. I have added your modify code in the script and i have created a table and included the columns Month, Calculation1 - 4, total but getting null values.

I have attached the qvf file with the script and sheet. 

main:
LOAD
Date(Date#([P Release Date], 'MM/DD/YYYY')) as [P Release Date],
Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Deleted On],
Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date]

inline [
P Release Date, Deleted On, PO Release Date
12/21/2022, -, 1/18/2023
12/23/2022, 2/27/2023, -
12/27/2022, -, -
1/4/2023, 1/24/2023, -
1/4/2023, -, 1/17/2023
1/4/2023, -, 2/28/2023
1/4/2023, -, 11/30/2023
1/6/2023, -, 2/9/2023
1/6/2023, -, 2/16/2024
1/11/2023, 5/10/2023, -
1/17/2023, 3/17/2023, -
1/17/2023, -, 4/11/2023
2/2/2023, -, 2/7/2023
2/2/2023, -, 6/21/2023
2/6/2023, 6/6/2023, -
2/15/2023, 9/11/2023, -
2/15/2023, -, 3/14/2024
3/2/2023, -, 5/3/2023
3/2/2023, -, 8/16/2023
3/2/2023, -, 9/22/2023
3/6/2023, -, 1/2/2024
3/30/2023, 4/11/2023, -
3/30/2023, 6/7/2023, -
3/30/2023, 8/7/2023, -
3/30/2023, -, 4/20/2023
3/30/2023, -, 8/29/2023
];

// Calculate Min and Max Dates
LET vMinDate = Num(Peek('MinDate', 0, 'DateBounds'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'DateBounds'));

DateBounds:
LOAD
Min([P Release Date]) as MinDate,
Max([P Release Date]) as MaxDate
RESIDENT main;

LEFT JOIN (main) //////////////// i have included this left join because i had getting error
// Generate Calendar using the variables
Calendar:
LOAD
Date(MonthStart('$(vMinDate)',IterNo()-1)) as MonthStart
AUTOGENERATE 1
WHILE MonthStart('$(vMinDate)',IterNo()-1) <= MonthStart('$(vMaxDate)')
;

LEFT JOIN (main)
LOAD
MonthStart([P Release Date]) as MonthStart,
// Calculation 1
If([P Release Date] <= MonthStart, If(IsNull([Deleted On]) and IsNull([PO Release Date]), 1, 0)) as Calc1,
// Calculation 2
If([P Release Date] <= MonthStart and IsNull([Deleted On]) and [PO Release Date] >= MonthStart, 1, 0) as Calc2,
// Calculation 3
If([P Release Date] <= MonthStart and [Deleted On] >= MonthStart and IsNull([PO Release Date]), 1, 0) as Calc3,
// Calculation 4
If([P Release Date] <= MonthStart and [Deleted On] >= MonthStart and [PO Release Date] >= MonthStart, 1, 0) as Calc4
RESIDENT main;

AggregatedData:
LOAD
MonthName(MonthStart) as Month,
Sum(Calc1) as [Calculation 1],
Sum(Calc2) as [Calculation 2],
Sum(Calc3) as [Calculation 3],
Sum(Calc4) as [Calculation 4],
Sum(Calc1) + Sum(Calc2) + Sum(Calc3) + Sum(Calc4) as Total
RESIDENT main
GROUP BY MonthStart;

///-------------------------------------------------------------

Thanks,

Sara.

[diegozecchini]

Hi @Sara_3  did it solve your issue? glad it helped

[Sara_3]

No, getting null values. That's why I have attached qvf file.

[Sara_3]

Hi Diego,

Please whenever you have time take a look into qvf file, I am getting Null values . I am really struggling this. 

Thanks,

Sara.

[diegozecchini]

Hi Sara! sory I am out of office for some days, I cannot see the file.
By the way here some suggestions:
-Ensure all date fields (P Release Date, Deleted On, and PO Release Date) are recognized as valid Qlik dates during the load script. Use Date(Date#(...)) consistently.
-Verify that the min and max dates are being correctly calculated and stored in vMinDate and vMaxDate.
-Check if the MonthStart field is properly populated and joined with the main table.

You can load a table in Qlik with just P Release Date, Deleted On, PO Release Date, and MonthStart to confirm the date parsing and calendar generation. Check if the conditions in your calculations are valid for the data.
Finally you can test your expressions for each calculation individually in the data model viewer.