Dear Community,
Lets say I want to monitor the number of competitor products at different customers and will update the information whenever I come home from a customer visit. This can be for some customers every few weeks, for others every few month or quarters.
I created a table (simplified) with "ID", "Customer ", "Competitor", "Product", "Amount of Products", "last update"
I was able to build a query showing me always the latest Record for each Customer and Product combination.
Now I would like to create a pivot chart showing me the number of competitor products (filter options on Customer, Competitor and Product) for each month. The rule should be: always take the latest record available up until end of the specific month.
Example 1:
The following table
Customer Competitor Product Amount of Products last update A Z Z1 5 20.02.2015 A Z Z2 10 10.04.2015 A Z Z1 10 01.06.2015
Should bring the following result in the pivot Chart:
January = 0
February = 5
March = 5
April = 15
May = 15
June = 20
July = 20
...
In a future step I also want to add a table with a price list by competitor product. Here I would also have a "last Update" field.
A second pivot chart should be created to show the estimated competitor revenues using the number of products in each month and the always valid competitor price in this month (following the same logic as above). Always taking the last valid price up to the last date of the month and multiplying it with the number of products
Example 2:
Product Price last update Z1 5$ 20.02.2015 Z2 10$ 10.04.2015 Z2 20$ 01.05.2015
Should produce the following Pivot Chart:
January = 0$
February = 25$
March = 25$
April = 125$
May = 225$
June = 250$
July = 250$
...
Any ideas on how to build the pivot chart (or any queries as basis for the pivot chart) for this problems. Your help would be very much appreciated.