Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all...
The attached spreadsheet shows what i am trying to achieve / what I am getting...
Any pointers greatly appreciated...
Thanks
Paul
In your example, click on Actual, and drag that dimension above the Group. You'll get the totals I think you want, just not the table layout I think you want. But I don't think pivot tables work the way I think you want them to work.
You can cheat, though I'm not sure I recommend it:
CONCATENATE LOAD * INLINE [
bcp_ProductFamily, bcp_Label
Total, Actual
Total, Budget
];
if(bcp_ProductFamily='Total',sum(total <bcp_CountryRegion,bcp_Label> bcp_Sales),sum(bcp_Sales))
Suppress zero values
hello Paul.
can you give an qvw example or small source data for creating your chart?
Looks to me like you just need to add a dimension for Actual vs. Target and move it to the top of your pivot table.
Have had some partial success with this late yesterday by doing the following...
Went down the road of creating a stand alone table - ended up with a new field called Budget Label which allowed me to show Actual and Budget as a Dimension (possibly what John was meaning?)...
*****Code Starts*****
// Budgets Table Header
// Step 1 - create a header table containing non value fields that I need in pivot
Budgets_CountryRegion:
LOAD
%CPBudgetKey,
bcp_Closed,
bcp_CountryRegion,
bcp_FinancialYear,
bcp_FinancialPeriod,
bcp_HistoricalMonthNumber,
bcp_ProductFamily
FROM Budgets_CountryRegion_ProductFamily.qvd (qvd)
WHERE (bcp_FinancialYear = '$(varFinancialYear_Current)')
AND (bcp_FinancialPeriod < '$(varFinancialPeriod_Current)');
// Sales Summary Table
// Step 2 - Summarise Actual Detail
TempSummary:
LOAD
TempCPBudgetKey AS BudgetKey,
'Actual' AS BudgetLabel,
sum(sin_Sales) AS Sales,
sum(sin_GrossProfit) AS GrossProfit,
sum(sin_Quantity) AS Units
RESIDENT SalesInvoice
WHERE (sin_FinancialYear = '$(varFinancialYear_Current)')
AND (sin_FinancialPeriod < '$(varFinancialPeriod_Current)')
GROUP BY TempCPBudgetKey;
Drop Field TempCPBudgetKey;
// Budgets Summary Table
// Step 2 - Summarise Budget Detail
TempSummary:
LOAD
%CPBudgetKey AS BudgetKey,
'Budget' AS BudgetLabel,
sum(bcp_Sales) * 1000 AS Sales,
sum(bcp_GrossProfit) * 1000 AS GrossProfit,
sum(bcp_Units) AS Units
FROM Budgets_CountryRegion_ProductFamily.qvd (qvd)
WHERE (bcp_FinancialYear = '$(varFinancialYear_Current)')
AND (bcp_FinancialPeriod < '$(varFinancialPeriod_Current)')
GROUP BY %CPBudgetKey;
// Join to Header Table
// Step 3 - Append Summary Detail to Header
LEFT JOIN(Budgets_CountryRegion)
LOAD
BudgetKey AS %CPBudgetKey,
BudgetLabel AS bcp_Label,
Sales AS bcp_Sales,
GrossProfit AS bcp_GrossProfit,
Units AS bcp_Units
RESIDENT TempSummary;
Drop Table TempSummary;
*****Code Ends*****
I am positive that there must be an easier way of doing this but this has got me part of the way there.
As I have said I am 99% of the way there - the attached spreadsheet shows how far.
I would like to have a totals column @ the right hand side.
I add this just now I only get a total total - I would like to see this split by Actual and Budget.
Apologies for not being able to post an example QVW...am in the middle of a few projects just now so time is at premium...
Will do my best to try and get an example posted if it helps.
I have managed to create an example QVW report which shows the issue that I now have...
On the right hand totals column I am getting the totals for Actual and Budget combined.
Would like to see these as two seperate columns.
Any helpd greatly appreciated.
In your example, click on Actual, and drag that dimension above the Group. You'll get the totals I think you want, just not the table layout I think you want. But I don't think pivot tables work the way I think you want them to work.
You can cheat, though I'm not sure I recommend it:
CONCATENATE LOAD * INLINE [
bcp_ProductFamily, bcp_Label
Total, Actual
Total, Budget
];
if(bcp_ProductFamily='Total',sum(total <bcp_CountryRegion,bcp_Label> bcp_Sales),sum(bcp_Sales))
Suppress zero values