I have a slight consfusion regarding Calculating Sales Changes in list report that displays summary sales changes for each product in a store.
Quotetodays_sales:
if ([Date] = ?Today Date?) then ([Sales]) else (0)
Quote
yesterdays_sales:
if ([Date] = ?Yesterday Date?) then ([Sales]) else (0)
Quote
change_in_sale:
([todays_sales]-[yesterdays_sales])/[yesterdays_sales]
Grouping is done on Product_Id. All of these data items have set the "Aggregate Function" to be calculated and "Rollup Aggregate Function" to be calculated.
Sample data:
QuoteProduct Date sales
product A Aug 29 2010 10
product A Aug 29 2010 5
product A Aug 28 2010 6
product A Aug 28 2010 8
If I calculate manually, I should get:
Quotechagne_in_sales =((10+5)-(6+8))/(6+8) = 0.071428571
Some reason all the change_in_sales for each product is either 0 or -1.
I am not sure why this is happening. I am not sure if I am doing it the right way when displaying change_in_sales in Summary level. Can someone help with this simple issue I am having.