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

    Set analysis, Intersection between month and Month-1

    Quentin MARACHE

      Hi everyone,

       

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

       

      2013-10-23_10h41_26.png

       

      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.

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

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