Set analysis, Intersection between month and Month-1

[#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(
{<Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, Year={$(=Only(Year(PRJ_ReloaDate)))} >}
)
*
p(
{1<Month={$(=only(Month(AddMonths(PRJ_ReloaDate,-1))))}, Year={$(=only(Year(AddMonths(PRJ_ReloaDate,-1))))} >})

>} Distinct OpportunityCD)

But it still give me wrong number or 0.

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

Please, Help-me !

Thanks !

Quentin.

[bbi_mba_76]

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)

[Gysbert_Wassenaar]

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)