Sum of value for defined period of activity

[sicilianif]

I have a user who is requesting the sum of sales for a product for the last three months of activity of a product.

For example;

     If a product (ABC in the data below) was sold in January, February, May, and June, the chart would display a total for February, May and June for that product. January would not be included.

     If another product (DEF in the data), was sold only in Feb and March, then it would show the total for those two months.

I have been trying to figure out how to do this in a chart with an expression and can not come up with a way. But, perhaps I have been over thinking it.

Sale date, Product, Qty

1/1/2013, ABC, 10

1/2/2013, ABC, 10

2/10/2013, ABC, 10

2/13/2013, ABC, 10

5/5/2013, ABC, 10

6/20/2013, ABC, 10

2/1/2013, DEF, 10

2/1/2013, DEF, 10

3/6/2013, DEF, 10

Thanks for any help,

Frank

[Gysbert_Wassenaar]

I think you need to add a field in the script. See attached example.

[sicilianif]

Your sample assumes that the 3 months of activity are consecutive and they are not always (for example product ABC). To fix your example to do what I need, it would be something like this:

join

load Product, if(isnull(monthstart(max(Saledate,3))),monthstart(min(Saledate)),monthstart(max(Saledate,3))) as RefDate

Resident Data

group by Product;

That being said, I am hoping to find a way to do this in the chart directly, so that I could make it dynamic.