6 Replies Latest reply: Mar 3, 2018 7:23 AM by THIAGO TEIXEIRA RSS

    Daily data compare in end of month

    Ahmad Hamdani

      Hello All,

       

      I have daily comparing data in any object, but I have some problem in my analysis in the end of month.

      basically my analysis is daily analysis (compare date to date), but in the end of month I wanna compare

      total this month vs total last month. I use this formula for daily analysis :

      =sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty).

       

      The problem when use that formula, when this month (February) max date is 28, but last month (January) max date is 31.

      analysis will be 28 Feb vs 28 Jan. can I change the formula when reaching end of month become 28 Feb vs 31 Jan.

       

      Does anybody know the tricky?

       

      Thank you

        • Re: Daily data compare in end of month
          THIAGO TEIXEIRA

          What about this:

           

          sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)


          Cheers

            • Re: Daily data compare in end of month
              Ahmad Hamdani

              Hello Thiago,

               

              Your formula is worked to find value in last month right?

              my issue is,

              how we find total end of month VS end of month for comparing achievement in the end of month.

              Example :

              =sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

              /

              sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)


              above formula result is : total 1-28 Feb 18 / total 1-28 Jan 18.

              All I want is to find result :

              total 1-28 Feb 18 / total 1-31 Jan 18.


              But also can see result by daily, for example max date is 20, and the formula become :

              total 1-20 Feb 18 / total 1-20 Jan 18


              Thank you

                • Re: Daily data compare in end of month
                  THIAGO TEIXEIRA

                  What if we use this:

                   

                  =sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                  /

                  sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

              • Re: Daily data compare in end of month
                THIAGO TEIXEIRA

                Based on your second condition I guess this will be helpful:

                 

                If (Max (Date_Fix)=MonthEnd (Max (Date_Fix)),

                sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                /

                sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                ,

                sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                /

                sum({<Date_Fix={">=$(=MonthStart (AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=(AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                )

                  • Re: Daily data compare in end of month
                    Ahmad Hamdani

                    Hello Thiago,

                     

                    Thank you for your sugggestion.

                     

                    with a little modification (underline), I get the logic. and here is my formula :

                     

                    If (Day(Max({<DESC= {'SALES'}>}Date_Fix))=Day(MonthEnd(Max({<DESC= {'SALES'}>}Date_Fix))),

                    (sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'STU'}>}Date_Fix))"},DESC= {'STU'},YEAR,MONTH,DAY>}sales_qty)

                    /

                    sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty))

                    ,

                    (sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

                    /

                    sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'STU'}>}Date_Fix),-1)))<=$(=AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty))

                    )


                    Regard.