Hi!

I am having problems with creating pivot charts used to track historical data from forms that don't have a date in them. I spoke twice to Support but they couldn't help me, so I am posting it here.

Let me provide you with two easy examples so you can see what I mean.

Lets say I have an online store (which is true!). I have a form (Products) where I describe each product including the price and the total number of units I have in stock. I update manually the field with the price each time a vendor changes them, and the number of units in inventory changes automatically each time a customer makes a purchase.

Example 1 

Lets say that I want to create a Pivot chart to track how the average price of my products change along the time. For example:

In December I had 2 products which costed 10 € each, so my average price was 10 €.

In January I added two more products, which cost 20 € each. My average price in January should be -> (10 + 10 + 20 + 20)/ 4 = 15 €.

What I want is to create a chart where my X axis is each month and my Y axis is the average price.

So the chart should reflect the following information:

December -> 10€
January -> 15 €

If I use added time as my X axis,  changes are reflected in the month where the product was created (not in the month when the changes were made). If I use modified time, the chart only reflects the last changes, but I can't see any historical trends. 

Example 2

Similar to the example 1, I would like to track how many units I have in stock along the time to see if my average stock increases, decreases or stays the same.

Lets say that in December I had an average of 20 units in stock, and in January I had an average of  40. As explained before, if I use added time as my X axis, changes are reflected in the month where the product was created (not in the month when the changes were made). If I use modified time, the chart only reflects the last changes, but I can't see any historical trends.

I was wondering even if this is tecnically possible. Does the Professional edition register automatically all the changes made in a field or only the last one? Can you figure out any workaround for this?

Also, I have seen that the Enterprise version has Record Audit features. Would it be possible to track historical data with that version?

Also, I was wondering if a change in the prices would affect the purchases already done before (I hope not!). I use the sales product subform to register the datails of the purchase. If tomorrow a product price increases or decreases,  will the price of already closed orders update automatically ? Because this can be a really huge limitation for me as it will distort completely my sales tracking.

Thank you for your help!!