4 Replies Latest reply: Oct 16, 2014 2:28 PM by Stefan Kunte RSS

    Compare complete months only


      Hi all,

       

      We load sales results monthly, and sometimes these come in the form of groups of weekly files.

       

      This is leaving me with an issue for my month on month growth figures.

      Ie - today it is comparing October 1-3 against September 1-3,

      Whereas what I want it to show is September growth over August as these are the last 2 complete months I have.

       

      Currently I'm using :

      =Num(Sum({<Month={$(=vMaxMonth)},Year={$(=vMaxYear)}>}Revenue),'$#,##0')

       

      to give me what should be last month.

      ---Variable for Max Month is a pretty standard : =Month(Max(invoice_date))

       

      And

      Num(Sum({<Month={$(=vPriorMonth)},Year={$(=vMaxYear)}>}Revenue),'$#,##0')
      For the prior month., where ---- month(addmonths(max(invoice_date) ,- 1))   is my vPriorMonth

       

      Does anyone know how I can ignore those months that are showing dates less than ...let's say the 26th?

       

      Thanks in advance

      T

       

       

       

       

        • Re: Compare complete months only
          Stefan Kunte

          Hi,

           

          you can try:

           

          month(addmonths(if( match(max(invoice_date) , monthend(max(invoice_date))),  max(invoice_date), addmonths(max(invoice_date),-1)),-1))

          Hope this helps.

           

          Best regards

          Stefan

            • Re: Compare complete months only

              Hi Stefan,

               

              Thanks, This is throwing out August for me.

               

              I did modify it to

               

              month(addmonths(if( match(max(invoice_date) , monthend(max(invoice_date))),  max(invoice_date), addmonths(max(invoice_date),-1)),0))

              And then it gives me September.

               

              The problem persists when a month is actually chosen though.

              If - for example, I chose April (which is complete)  then these expressions give me February and March respectively, which is not what I'm after.

               

              Thanks though

               


                • Re: Compare complete months only
                  Stefan Kunte

                  Hi,

                   

                  I guess it's due to your invoice_date. how is your calendar programmed ? Full date range for every month? Then replace invoice_date with the key date in the calendar.

                   

                  edit: this is the formula for your prev Month Variable. For your max Month Variable use:

                   

                   

                  month(if( match(max(invoice_date) , monthend(max(invoice_date))),  max(invoice_date), addmonths(max(invoice_date),-1)))

                   

                  Change the date to your calendar date field.

                   

                  If not it becomes complicated because you need to replace monthend with lastworkdate(start_date, no_of_workdays {, holiday}), but as you can see second parameter will require addtional work.


                  Hope this helps

                   

                  Best Regards

                  Stefan