Pivot Table Help

[pkelly]

Hi all...

The attached spreadsheet shows what i am trying to achieve / what I am getting...

Any pointers greatly appreciated...

Thanks

Paul

[sparur]

hello Paul.

can you give an qvw example or small source data for creating your chart?

[johnw]

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.

[pkelly]

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.

[pkelly]

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.

[johnw · Accepted solution]

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