summarize fact data with moving/dynamic dates

[dbaksa_1]

I'm trying to summarize sales data for product for and annual period from the first invoice sale for the product. I'm using set analysis and as long as I have a specifc item selected the data is accurate. If I have no product selected the summary is predicated on the min(GL DATE) in the fact table and not the min(GL Date) of of the product.

I'm not sure if set analysis is the way to go on this. Any help will be apprecuiated.

[CODE]

SUM

({$<[AFS.GL DATE] = {">=$(=MIN([AFS.GL DATE])) AND <=$(=ADDMONTHS(MIN([AFS.GL DATE]),13))"}>}[AFS.EXTENDED REVENUE]

)

[\CODE]

[vgutkovsky]

Daniel,

The problem with set analysis is that it's evaluated globally, not over dimensions. That is why min() returns the global min unless you manually select a product. For your purposes, I would think you need to use AGGR instead of set analysis. Something like:

sum(
 if([GL Date] >= date(max(aggr(min([GL Date]),ProductID))) and [GL Date] <= addmonths(date
 (max(aggr(min([GL Date]),ProductID))),13), [Extended Revenue])
)

Regards,

[dbaksa_1]

I have tried using the if statement but I had not thought to use the AGGR function as part of the solution. I will give it a try and let you know how it works.

Thanks.

[vgutkovsky]

Sorry, I just realized the statement above will give you a syntax error. Try this one instead:

sum(
 if([GL Date] >= aggr(min([GL Date]),ProductID) and [GL Date] <= addmonths(aggr(min([GL Date]),ProductID),13), [Extended Revenue])
)

Let me know if it works.

[dbaksa_1]

I was looking at your code with an associate this morning because the original did not work. We did end up with the code you just sent and still cannot seem to make it work. It appears that the expression does not evaluate the date range and only a subset of the data is actually summarized.

I have decided to take a simple path for now. I'm just going to use a date list box and a pivot table. I need to get something to the user today.

However I have not given up yet and will return to this later today or tomorrow.

Thanks for your help...