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 :



      to give me what should be last month.

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



      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






        • Re: Compare complete months only
          Stefan Kunte



          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


            • 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



                  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