7 Replies Latest reply: Apr 15, 2014 4:16 AM by Dariusz Mielczarek RSS

    Max(TOTAL <>) statement within If statement

    Atsushi Saijo

      Dear Sirs,

       

      *Attached is the QV file in the very issue:

       

      Below expression does not work.

      • Sum(if(DocDate<=MonthEnd(Today(0),-$(Y))

                and MonthName(Date(Max(TOTAL <ProductSerial>

                    if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) )))=Monthname(MonthEnd(Today(0),-$(Y)))

                ,Weight))

       

      However, simple expression works as such:

      • Sum(if(DocDate<=MonthEnd(Today(0),-$(Y))

                ,Weight))

      • Date(Max(TOTAL <ProductSerial>

                    if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) ))

       

      The requiement of the expression is:

      • SumWeight only if the maximum DocDate is in reporting range;
      • Reporting Range is defined as <=MonthEnd(Today(0), -$(Y)), where Y is integer, which user input.

       

      [Example] Please see below example: where 3 DocDate exists.

      Error 4.jpg

      Current reporting month is March. (User selected 1)

      Error 5.jpg

      Here, the maximum DocDate is 12.03.2014. This document is also in the reporting month: i.e.

      • InMonthToDate(

           Date(Max(TOTAL <ProductSerial>

           if( Date(DocDate)<=MonthEnd(Today(0),-$(Y)),DocDate) )), MonthEnd(Today(0), -$(Y)) ) = -1

       

      This must result in 1.

       

      On the contrary, there would be such case (maximum date is not in the March, but either future date such as April, or older date.)

      Error 6.jpg

      In such case, the Expression must exclude counting the Weight. Althoguh total is 1, this must result in 0 since maximum DocDate is not in March 2014.

       

      Would you possibly advise how we could implement this? I'd appreciate for your wisdom.

       

      *The result of this report must produce 818 instead of 814. If 814, it means Expression has calculated only on the DocDate in current month.

       

      *Specifically, this is the stuation where SAP VBRK/P (SD) table making sum per product serial. DocDate is FKDAT. Document Action Type is either billing cancellation or invoice issue.

        • Re: Max(TOTAL <>) statement within If statement
          Dariusz Mielczarek

          Hi Atsushi,

           

          Put simply, you need to get the sum of all Weights but only for these SerialNubers, whose maximum date is not greater than the end of the month specified by the user?

           

           

          regards

          Darek

            • Re: Max(TOTAL <>) statement within If statement
              Atsushi Saijo

              Dear Darek, I deeply appreciate for your support.

              Yes, you are right. The key is:

               

              • The only ProductSerial, which has any DocDate (regardless of if it is maximum or minimum....) we must sum the Weight.For the rest of the entries, which does not have DocDate within the month, we must exclude it from summing.

               

              Please assume that user aims Reporting Month as March (shift -1) in the situation like below:

               

              ProductSerial; DocDate; Weight

              000001; 01.02.2014; Issue; +1

              000001; 01.03.2014; Cancel; -1

              000001; 04.03.2014; Issue; +1

               

              Then total is +1 (summing its total history in the source system).

               

              But if this is such, we must exclude from the summation.

               

              ProductSerial; DocDate; Weight

              000002; 01.02.2014; Issue; +1

              000002; 10.04.2014; Cancel; -1

              000002; 11.04.2014; Issue; +1

               

              So this will calculate Monthly Whole-sales figure. Why we cannot just use the maximum date of issue date? It is for back-dateability. If user has issued the invoice, but if it were cancelled in another month, user wishes to see the issued invoice as +1 even if it were reversed in later time.

               

              I'd appreciate for your feedback in advance.

               

              Atsushi

               

              PS. And this would be great contribution for any QV practitioners who creates reports in SAP FI or SD area based on VBRK/VBRP table extractions.

                  • Re: Max(TOTAL <>) statement within If statement
                    Atsushi Saijo

                    Dear Darek, I appreciate for your always excellent follow-ups. Besides I appreciate your all expression-statements, which provides with new source of learning.

                     

                    I have examined the new statement:

                    • if(count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y+1)))"}>}DocDate)=0,sum(Weight)


                    [Detail Examination]

                      if(   count(

                              {<DocDate

                                   ={">=$(=MonthStart(Today(0),$(=-Y+1)))"}>}             

                              DocDate)=0,

                     

                             //count for DocDate for those DocDate >= reporting month, and if it were zero,

                     

                       sum(Weight)    )     

                             

                              //sum up all weight for above

                     

                    So if we change the condition statement COUNT(....) >0, 819 is produced!

                    • if(count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y+1)))"}>}DocDate)>0,sum(Weight)

                     

                    For Monthly Wholesales purpose, COUNT condition could be further changed as:

                    • if(count({<DocDate={">=$(=MonthStart(Today(0),$(=-Y)))<=$(=MonthEnd(Today(0),$(=-Y)))"}>} DocDate)>0 ,sum(Weight) )

                     

                    This resulted in 818, identical to what source system produced. I'd like to designate yours as correct answer and I'd appreciate for all help in this enquiry.

                     

                    *To clarify the logic, why COUNT produces the right condition? I was thinking somehow we had to create an array of ProductSerial, with which we execute summation. But this method do not need direct selection of ProductSerial. Why does it produces the right result?