Set analysis help

[dafnis14]

Hi,

I would appreciate some help with set analysis for the following case:

2 data tables:

• - Inventory data with the fields SupCode, Inventory
• - Sales Data with the fields Sales_Date, Sales

A straight table with the dimensions:

• - Supplier Code
• - Supply Date

For each supply date, the table should show total inventory  for the Supply Date,  and total sales for a period of 30 days previous to that Supply date.

To calculate the sales, I wrote the following expression:

aggr(sum({$<Sales_Date= {">$(= date(Supply_Date -30)) <=$(=date(Supply_Date))"}>} Sales), SupCode)

But it works only if a certain supply date is selected. Otherwise, null values are displayed.

Can someone assist me?

Thanks!

[Peter_Cammaert]

A set in set analysis is calculated only once, at the start of chart recalculation and not row-by-row.

You can use an AsOf table to solve this problem. See this document Calculating rolling n-period totals, averages or other aggregations for an explanation of why set analysis doesn't work and how AsOf tables can solve most of these rolling period-related problems.

Best,

Peter

[dafnis14]

Great!

Thanks!