ClearCollect(_data01,
{id: 1, recordDate: "08-01-2024", name: "John", status: "InP", amount: 1000},
{id: 1, recordDate: "08-01-2024", name: "Sarah", status: "InP", amount: 500},
{id: 1, recordDate: "09-01-2024", name: "Steve", status: "Rej", amount: 20000},
{id: 1, recordDate: "10-01-2024", name: "John", status: "Prc", amount: 3000},
{id: 1, recordDate: "10-01-2024", name: "John", status: "Prc", amount: 600},
{id: 1, recordDate: "10-01-2024", name: "Steve", status: "AWP", amount: 8000},
{id: 1, recordDate: "11-01-2024", name: "Sarah", status: "Ret", amount: 6000},
{id: 1, recordDate: "11-01-2024", name: "Sarah", status: "Prc", amount: 10000}
);
It seems like you want to group the data and view the process by this grouping, no pivot or transposing of data is needed. - Group the data by name:
// Group the data by 'name' and add columns for each status count
ClearCollect(_tpdata,
AddColumns(
GroupBy(_data01, name, 'NamesGrouped'), // Group by 'name' and store grouped data in 'NamesGrouped'
'Awaiting payment', CountRows(Filter(NamesGrouped, status = "AWP")), // Count 'AWP' status
'In progress', CountRows(Filter(NamesGrouped, status = "InP")), // Count 'InP' status
'Processed', CountRows(Filter(NamesGrouped, status = "Prc")), // Count 'Prc' status
'Reject', CountRows(Filter(NamesGrouped, status = "Rej")), // Count 'Rej' status
'Return', CountRows(Filter(NamesGrouped, status = "Ret")), // Count 'Ret' status
'Total general', CountRows(NamesGrouped) // Count total records for each name
)
);
Once your data is grouped and counted you only need to add the row total, for this you need another collection
// Create a collection for the total row with sums for each status
ClearCollect(_totalRow,
{
name: "Total", // Label for the total row
'Awaiting payment': Sum(_tpdata, 'Awaiting payment'), // Sum of 'AWP' status counts
'In progress': Sum(_tpdata, 'In progress'), // Sum of 'InP' status counts
'Processed': Sum(_tpdata, 'Processed'), // Sum of 'Prc' status counts
'Reject': Sum(_tpdata, 'Reject'), // Sum of 'Rej' status counts
'Return': Sum(_tpdata, 'Return'), // Sum of 'Ret' status counts
'Total general': Sum(_tpdata, 'Total general') // Sum of total records for each name
}
);
Now a final one to add the total row
// Combine the main data collection with the total row
ClearCollect(_finalData, _tpdata); // Copy _tpdata to _finalData
Collect(_finalData, _totalRow); // Add the total row to _finalData
Result
Initial Data (_data01)
Final data (_finalData)
I hope it helps you, have fun.
R
