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

    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 )


      OpportunityCD = p(
      Month={$(=Only(Month(MonthName(PRJ_ReloaDate))))}, Year={$(=Only(Year(PRJ_ReloaDate)))} >}
      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 !



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


          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
            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.



            Month_AsOf, Month, Period

            janv. , janv. , Current

            févr., févr., Current

            févr., janv., Previous

            mars, mars, Current

            mars, févr, Previous



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

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