Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using group by to create a P&L but it is not working as I would expect.
See attached example
I don't understand what is the result you expect...
If you want a table like this:
Try the following :
Calculations:
LOAD [Type],
Sum(Amount) as [Gross Margin]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;
JOIN (Calculations) LOAD Type,
Sum(Amount) as [Profit before Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;
join(Calculations) LOAD [Type],
Sum(Amount) as [Profit After Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;
If you want the following :
Try :
JOIN(FinancialResultsTable) LOAD
'Gross Margin' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;
JOIN(FinancialResultsTable) LOAD
'Profit before Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;
JOIN(FinancialResultsTable) LOAD
'Profit After Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;
I don't understand what is the result you expect...
If you want a table like this:
Try the following :
Calculations:
LOAD [Type],
Sum(Amount) as [Gross Margin]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;
JOIN (Calculations) LOAD Type,
Sum(Amount) as [Profit before Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;
join(Calculations) LOAD [Type],
Sum(Amount) as [Profit After Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;
If you want the following :
Try :
JOIN(FinancialResultsTable) LOAD
'Gross Margin' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;
JOIN(FinancialResultsTable) LOAD
'Profit before Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;
JOIN(FinancialResultsTable) LOAD
'Profit After Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;
The attached example is what I was trying to do.
Thanks for your help