Calculated fields in Script load / table columns by month
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 :
Hi Diego' I have checked stet by step as you mentioned and i found that the vMinDate and vMaxDate are not being correctly calculated and stored.
second, when i have load the following script i got the error 'MonthStart' field not found so thats means MonthStart field is not properly populated and joined with the main table.
LEFT JOIN (main) LOAD MonthStart([PReq Release Date]) as MonthStart, // Calculation 1 If([PReq Release Date] <= MonthStart, If(IsNull([Deleted On]) and IsNull([PO LI Release Date]), 1, 0)) as Calc1, // Calculation 2 If([PReq Release Date] <= MonthStart and IsNull([Deleted On]) and [PO LI Release Date] >= MonthStart, 1, 0) as Calc2, // Calculation 3 If([PReq Release Date] <= MonthStart and [Deleted On] >= MonthStart and IsNull([PO LI Release Date]), 1, 0) as Calc3, // Calculation 4 If([PReq Release Date] <= MonthStart and [Deleted On] >= MonthStart and [PO LI Release Date] >= MonthStart, 1, 0) as Calc4 RESIDENT main;
I have run the following script , now I have checked the vMinDate and vMaxDate variable correctly calculated and stored in the variable.
- MonthStart field is properly populated and joined with the main table.
but when i have created a table with Month. Calculation1, calculation2, calculation3 and calculation 4 fields I got the zero (0) values for calculations.
DateBounds: LOAD date(Min([P Release Date])) as MinDate, date(Max([P Release Date])) as MaxDate RESIDENT main;
// Calculate Min and Max Dates LET vMinDate = num(Peek('MinDate', 0, 'DateBounds')); LET vMaxDate = num(Peek('MaxDate', 0, 'DateBounds'));
// // 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, //[P Release Date], // Calculation 1 If([P Release Date] <= MonthStart([P Release Date]), If(IsNull([Deleted On]) and IsNull([PO Release Date]), 1, 0)) as Calc1, // // Calculation 2 If([P Release Date] <= MonthStart([P Release Date]) and IsNull([Deleted On]) and [PO Release Date] >= MonthStart([P Release Date]), 1, 0) as Calc2, // // Calculation 3 If([P Release Date] <= MonthStart([P Release Date]) and [Deleted On] >= MonthStart([P Release Date]) and IsNull([PO Release Date]), 1, 0) as Calc3, // // Calculation 4 If([P Release Date] <= MonthStart([P Release Date]) and [Deleted On] >= MonthStart([P Release Date]) and [PO Release Date] >= MonthStart([P Release Date]), 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, ; -------------------------------------Script End--------------------------------
Frlm your script and the issue you described, I believe the problem lies in the calculations logic or the linking between the main table and the Calendar table. You need to troubleshoot step by step.
First of all verifi that the vMinDate and vMaxDate variables are correctly calculated and passed to the Calendar table. Use the debug feature or TRACE to check the values.
TRACE vMinDate: $(vMinDate); TRACE vMaxDate: $(vMaxDate); Check if the Calendar table is generating MonthStart values that match those in the main table. Use a Table Box in Qlik to display and compare the MonthStart field.
The join uses MonthStart([P Release Date]) to link with MonthStart in the Calendar. Let's check the MonthStart field is calculated correctly in both tables that there are no formatting mismatches (e.g., one is a string, the other is a date). To debug:
Your conditions in Calc1, Calc2, Calc3, and Calc4 rely on MonthStart([P Release Date]). Ensure these calculations are resolving correctly.
To do it you can use temporary fields to validate intermediate steps:
If(IsNull([Deleted On]), 'Null', 'Not Null') as DeletedOnStatus, If(IsNull([PO Release Date]), 'Null', 'Not Null') as POReleaseStatus Test a simplified version of the calculations to isolate the problem.
If the Calc1, Calc2, etc., are showing 0 values, it’s likely due to a mismatch in your join or faulty calculation logic. Test if the fields exist in main after the join:
Inspect your inline table and ensure all fields are populated correctly: Are there missing or invalid dates in [P Release Date], [Deleted On], or [PO Release Date]? Does the inline table data cover the expected range of months (vMinDate to vMaxDate)?
Ensure MonthStart is grouped correctly in the final aggregation. If there’s an issue with the group-by key, you might see incorrect aggregation.
Here’s a slightly modified version of your calculations for debugging:
LEFT JOIN (main) LOAD MonthStart([P Release Date]) as MonthStart, If([P Release Date] <= MonthStart([P Release Date]), 1, 0) as TestCalc1, If(IsNull([Deleted On]), 1, 0) as IsDeletedNull, If(IsNull([PO Release Date]), 1, 0) as IsPONull RESIDENT main; Test if TestCalc1, IsDeletedNull, and IsPONull populate correctly before incorporating them into your final calculations.
After troubleshooting each step abovre
Verify the join condition between main and Calendar. Test the aggregation logic by creating a simplified version of the table. Gradually reintroduce complex calculations to pinpoint the issue. Let me know how it goes!
1- i have checked the vMinDate and vMaxDate and i got the expected result:
VMinDate = 12/21/2022
vMaxDate = 3/30/2023
2- when i have loaded the calendar and compare the main (P Release Date) with MonthStart field i got the wrong output . I have noticed that for every monthStart (12/1/2022,1/1/2023,2/1/2023,3/1/2023) associated with multiple P Release Date regardless the Month and this is the problem i think.
here are few :i got the 52 rows of data instead of 26.
P Release Date MonthStart
12/21/2022 12/1/2022
1/4/2023 12/1/2022
2/6/2023 12/1/2022
3/30/2023 12/1/2022
i think the issue is with the following script:
Calendar: LOAD Date(MonthStart('$(vMinDate)',IterNo()-1)) as MonthStart AUTOGENERATE 1 WHILE MonthStart('$(vMinDate)',IterNo()-1) <= MonthStart('$(vMaxDate)') ;
Calendar: LOAD Date(AddMonths('$(vMinDate)', IterNo() - 1), 'MM/DD/YYYY') as MonthStart AUTOGENERATE 1 WHILE AddMonths('$(vMinDate)', IterNo() - 1) <= '$(vMaxDate)';
AddMonths: Instead of relying on MonthStart inside the WHILE condition, this approach calculates MonthStart directly using AddMonths, which ensures accurate comparisons. Including 'MM/DD/YYYY' ensures consistent formatting, reducing mismatches in joins and by directly comparing '$(vMaxDate)', you should avoid redundant rows.
