Calculate sum based on start date

[#1]

I have two tables - sales and location.

Location: Has Location Code and start Date

Sales : Has Location Code and Sales for last year

I loaded sales as cross table and set up a master calendar.

I have managed to calculate the sum of sales for the last 4 months. But for this example I have hardcoded 4. I actually need to go back as far the start month for each location. For example, Location 11 needs to have the sales calculated from period 201406. How do I achieve this with the expression I have currently used? =sum({$<[PeriodID] = {">$(=Max([PeriodID])-4)"}>}Sales)

I have attached the file with this post. I also need to consider new sales data will be added for 2015.

[#2]

Any help guys ? I have been stuck with this problem for a while now

[arulsettu]

try like this

=sum({$<[PeriodID] = {">$(=monthstart([PeriodID])-4)"}>}Sales)

[sujeetsingh]

I am not able to grasp what you need ?

Please explain according to your given sample objects that what functionality you need.

[#5]

In the expression , I have hardcoded the number 4.  As in calculate sales for the last four months,

However, this number needs to be generated based on the month of start date of the location. In the example shown, Location code 11 has a start date of 6/1/2008. The month is june. So I need to calculate the sales from june to current ( last seven months)

Please let me know if you have additional questions

[sujeetsingh]

just convert periodID as Date first. Then you have to calculate the difference of the periodid month and the Startdate month.Now you can use these autogenerated difference in  months in your  expression.

Or just join both tables and Calculate a flag as

=if(PeriodID>=StartDate,1,0) as CalculateFlag

Now use expression as Sum({<CalculateFlag={'1'}>}Sales)

[#7]

Can you please provide the working example? I have attached the file in my original post.

[#8]

Update:

I got it to work using the expression = sum({$<Month={'>=$(=num(month([Start Date])))'}>}Sales)

However this will not work when I show all locations in a table

But when I select a particular start date, it works

I gather I probably need to use aggregation by start date somewhere ? I am getting close but still not there yet. Attaching the updated file. Any help would be greatly appreciated!

Thanks,

Praveen

[maxgro]

sum(if(makedate(left(Period,4),Right(Period,2))>[Start Date], Sales))

[#10]

Hi Massimo

Your expression is not working as I expected. I have attached the sample application for your reference.

I did get the expression to work but as i mentioned a little earlier, i need to select a specific start date to have it displayed.