2 Replies Latest reply: Oct 23, 2013 6:06 AM by Gysbert Wassenaar

# Set analysis, Intersection between month and Month-1

Hi everyone,

I'm stuck in this problem since one day and i don't find any solution...

I want to count old opportunities ("Anciennes") which still exist in month dimension AND in month-1. For exemple, for September, i want to count opportunites which exist in September and "Août" (August).

It works when i select one month and one Year, but not when i select only one Year

I think it's an Set Analysis with an Intersection operation. So i try:

count (distinct  {<OpportunityCD = P ({<Year={\$(=Only(Year(PRJ_ReloaDate)))}>} OpportunityCD ) >*<OpportunityCD = P ({<Year={\$(=Only(Year(PRJ_ReloaDate)-1))}>} OpportunityCD ) > } OpportunityCD )

And

Count({\$<
OpportunityCD = p(
{<
)
*
p(
{1<

>} Distinct OpportunityCD)

But it still give me wrong number or 0.

Maybe i'm on right way, maybe, absolutly not...

Thanks !

Quentin.

• ###### Re: Set analysis, Intersection between month and Month-1

Hi,

set analysis could be used to "ignore/force" selections on fields.

In your case it is possible to work on the script to add the previous month value or with inter record funcionts (like above)

• ###### Re: Set analysis, Intersection between month and Month-1

Your problem is that you're using month as dimension. The set analysis expression doesn't create a set per row, but per chart. It can't look at other rows, so the row for september can't calculate anything from august. That's why it works if you select a month and a year, but not if you select only a year.

What you could try is to create a new field in the script that retrieves the opportunities of the previous month.

Or a kind of AsOf table that links each month with itself and with the previous month.

AsOf:

Month_AsOf, Month, Period

janv. , janv. , Current

févr., févr., Current

févr., janv., Previous

mars, mars, Current

mars, févr, Previous

...etc

You can then use Month_AsOf as dimension instead and expressions like:

count({<Period={'Current'},OpportunityCD=P({<Period={'Previous'}>}OpportunityCD)>}distinct OpportunityCD)