10 Replies Latest reply: Nov 3, 2012 3:36 PM by Thor Hansen RSS

    Calculating R12 values

      Hi guys

       

      I am trying to create a chart based on the table attached, where the "Weighted amount in SEK" should be calculated into R12 values.

      And if I select to display 2010 to 2012, it should still calculate Feb 2009 to Jan 2010 to display in Jan 2010 as first value in the chart.

       

      Anyone there who can help me?

       

      I have tried searching around without any luck.

       

      Best regards

      Thor

        • Re: Calculating R12 values
          Gysbert Wassenaar

          something like this perhaps:

          sum({1<[Expected_Decision_Date]={">=01/02/2009<=31/01/2010"} >}[Weighted amount in SEK])

            • Re: Calculating R12 values

              Thanks for the reply, but it is not exactly what I am looking for.

              I want to see the rolling 12 Weighted Amount value per Account Manager per Month, like in my example below where each line is the R12 value for one account manager.

               

              Regards

              Thor

              2012-11-01_18-26-21.jpg

                • Re: Calculating R12 values
                  Teemu Pitkänen

                  Hey,

                   

                  Assuming you have some sort of calendar in your data model, you could add a YearMonth field to it and then create a new calendar (let's call it RollingCalendar for now). In that calendar you'll create a field called something like RollingYearMonth and link the original YearMonths to it. So for example, the RollingYearMonth value 201206 should be linked to 12 different YearMonth values (201107, 201108, 201109... 201206).

                   

                  If you then use this new RollingYearMonth as a dimension in your chart, you can calculate R12 averages, sums etc. pretty easily.

                   

                  Hope this helps!

                   

                  -Teemu

              • Re: Calculating R12 values

                Oh, and one more question. 

                Instead of the graph sinking when there are no transactions after (in this case 2012-05 for the blue line), it just stops.  It should in fact calculate the R12 value every month up until the end of my selection which is 2012-09.  Any idea why this is happening?

                 

                2012-11-03_11-03-08.jpg

                 

                Regards

                Thor

                  • Re: Calculating R12 values
                    whiteline _

                    If you transform your chart into a table you will see.

                    There is no dimension values with dates more than 2012-05 for 'blue'.

                     

                    It's a common question in the community.

                    I suggest you to add the nulls for each Date-Manager combination. It's not so hard and not so much data:

                     

                    AllCombinations:

                    LOAD distinct

                    Expected_Decision_Date

                    Resident Data;

                     

                    left join(AllCombinations)

                    LOAD distinct

                    [Account Manager]

                    Resident Data;

                     

                    Concatenate(Data)

                    LOAD

                    *

                    Resident AllCombinations;

                     

                    drop table AllCombinations;

                      • Re: Calculating R12 values

                        Hi Whiteline,

                         

                        Sorry, I am a complete nb, so I would not get this to work.

                        This is how my script looks.  Would it still be possible to use your suggestion by renaming Expected_Decision_Date to CalendarDate and [Account Manager] to AccountManager?

                         

                        Or do I need to include every field?

                         

                        LOAD OpportunityName,
                              Status,
                              OF_Status,
                              Pipeline,
                              Probability,
                              IIP,
                              Unweighted,
                              UnweightedSEK,
                              Weighted,
                              WeightedSEK,
                              UnweightedOpp,
                              UnweightedOppSEK,
                              WeightedOpp,
                              WeightedOppSEK,
                              Currency,
                              SEK_Rate,
                              Date as CalendarDate,
                              Year,
                              Month,
                              YearMonth,
                              CreateDate,
                              EditDate,
                              LeadDate,
                              PipelineChangeDate,
                              StatusChangeDate,
                              DateBecameCustomer,
                              AccountManager,
                              Region,
                              Customer,
                              CustomerID,
                              CustomerType,
                              Channel,
                              InfluencedSale,
                              Partner,
                              PartnerID,
                              ProductFamily,
                              Segment,
                              Product,
                              MainProduct,
                              ProductType,
                              Process,

                              Industry,

                              SubscriptionPeriod,
                              Financing,
                        FROM