2 Replies Latest reply: Apr 14, 2014 8:17 AM by Atsushi Saijo Branched from an earlier discussion. RSS

    The sum of Weight with only Max(DocDate) on the current reporting month

    Manish Kachhia

      Like this?

       

      Update :

      Check 2nd file also...

        • Re: The sum of Weight with only Max(DocDate) on the current reporting month
          Manish Kachhia

          Are my reply correct ? what you want to know ?

            • Re: The sum of Weight with only Max(DocDate) on the current reporting month
              Atsushi Saijo

              Dear Manish, I apologize for long lapse of time until I formalize the feedback. At the same time, I deeply appreicate for your fast response.

               

              Would you possibly advise how we could construct logic in line with below:

              • Reporting month is defined as month(Today(0) - $(Y)), where Y is integer, which user input for month-shift.
              • Sum weight, which belongs in the reporting month or before, only for those Maximum Date is in the current reporting month. This is Max(TOTAL <ProductSerial> if(DocDate<=MonthEnd(Today(0),-$(Y)),DocDate)).
              • Maximum Date is the date, which is largest until the reporting month. In the source data, there might be date in April, but since reporting month is March, the max date within March is maximum date.

               

              I have prepared a sample QV as attached.

               

              You see the input field, where you may input 1, 2 ..... This will shift the reporting month.

               

              I'd like to produce such:

               

              Sum(if(

                   InMonthToDate(

                          Max(TOTAL <ProductSerial> if(DocDate<=MonthEnd(Today(0),-$(Y)),DocDate)),

                          MonthEnd(Today(0),-$(Y)),0),

                   //if maximum date resides within the reporting month;

              Weight))

                   //sum all the weight until the reporting month <= I am not certain if this works

               

              Above does not work....I appreciate for your feedback if any.