Forum Discussion
Solved
Pivot with a mix of static and transactional data
I have some excel sales data from an SQL view.
The data is a merge of monthly sales transactions and the static customer information.
Currently I have a PIVOT (tabular format) that display the customer account code and current balance owed along with accumulated figures for each transaction type. A timeline is used to control the date range for the accumulated figures. All very standard PIVOT table work.
As per "normal" any pivot table data is only displayed if the date matches the timeline range.
Is there any way to display all customers with a balance even if they have no transactions in the timeline range? eg Customer A1034 for month July. They have a balance owed but no transactions in July. I am guessing that the pivot table data would need to be used in other lookup to get a solution.
I have attached a sample workbook.
Hi WillSmyth
yes, it's possible datamodel / Power Pivot.
You need to load the transaction table into the datamodel (menu "Power Pivot | Add to Data Model")
In the datamodel, you need to create a calendar table (menu "Design | Date Table | New)
Then create a relation between transaction table and calendar table based on the date field.
Create a pivot table from the datamodel (menu "Insert | PivotTable | From Data Model")
Add a timeline and use the date field from the calendar table (not from the transaction table!)
Open the PivotTable Option, Display tab and tick the checkboxes "Show items with no data on rows/columns"
I have done this already in the attached workbook.
Kind regards,
Martin
Hi WillSmyth
yes, it's possible datamodel / Power Pivot.
You need to load the transaction table into the datamodel (menu "Power Pivot | Add to Data Model")
In the datamodel, you need to create a calendar table (menu "Design | Date Table | New)
Then create a relation between transaction table and calendar table based on the date field.
Create a pivot table from the datamodel (menu "Insert | PivotTable | From Data Model")
Add a timeline and use the date field from the calendar table (not from the transaction table!)
Open the PivotTable Option, Display tab and tick the checkboxes "Show items with no data on rows/columns"
I have done this already in the attached workbook.
Kind regards,
Martin
- Hi Martin
This works beautifully but I don't understand basing the relationship on the DATE field. Could you explain? (I will be marking as best response regardless!)Hi WillSmyth
this is one of the advantages of calendar tables. They contain every single date and not only dates where any transaction has been registered in your table. Therefore the can show also those records with no transactions.
If you create the timeline with the date field from your transaction table, you will miss out account numbers that do not have any transactions at all (e.g. A1025, A1034, A1050...)
Kind regards,
Martin
