First customize your load script
// Load your data
Table:
LOAD
Sr_number,
Month_Closure,
Category
FROM [your_data_source.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
// Create a mapping table for month order
MappingTable:
Mapping LOAD
Month_Closure,
MonthOrder
Inline [
Month_Closure, MonthOrder
Oct-2022, 1
Nov-2022, 2
Dec-2022, 3
Jan-2023, 4
Feb-2023, 5
];
// Join the mapping table to the main data
Table:
LOAD
Sr_number,
Month_Closure,
Category,
MonthOrder
RESIDENT Table;
DROP TABLE MappingTable;
// Create a pivot table
PivotTable:
PivotTable
LOAD
Category,
'Oct-2022',
'Nov-2022',
'Dec-2022',
'Jan-2023',
'Feb-2023'
RESIDENT Table;
DROP TABLE Table;
// Calculate the count for each combination of Category and Month
CountTable:
LOAD
Category,
'Oct-2022',
'Nov-2022',
'Dec-2022',
'Jan-2023',
'Feb-2023',
Count(Sr_number) as Count
RESIDENT PivotTable
GROUP BY Category, Month_Closure;
DROP TABLE PivotTable;
// Calculate the percentage
PercentageTable:
LOAD
Category,
'Oct-2022',
'Nov-2022',
'Dec-2022',
'Jan-2023',
'Feb-2023',
Count / Sum(Total Count) as Percentage
RESIDENT CountTable
GROUP BY Category;
DROP TABLE CountTable;
Now count is
=Sum(Count)
& Percentage is
=Num(Sum(Percentage), '0.00%')
Will get auto-customized based on the placed you use. Try in your tables.
