1 Reply Latest reply: Apr 14, 2014 7:51 AM by Atsushi Saijo Branched from an earlier discussion. RSS

    Only sum Weight for those Product Serials, which has the Maximum Date in the Reporting Month

    Toni Kautto

      Without seeing your application or data model I find it a bit hard to determine exactly what the best solution/expression would be.

       

      My approach is to start by defining the report period. In my example this is through a calendar island. With the report period locked to always one selected value, it is possible to always calculate the report end date by just referring to the field ReportEndDate.

       

      In the straight table the first thing is to identify which rows are included in the report period, which can be visualized with an simple comparison.

       

      =if(DocDate<=ReportEndDate, DocDate)

       

      The next thing is to identify the maximum date for a specific product serial. The easiest way to do this is to use the Max() function and aggregate the value over the ProductSerial field.

       

      As Max() returns a numeric value, an additional Date formatting is required to keep the value readable. This format could also be accomplished by formatting the number format in the chart properties

       

      =Date( Max(TOTAL <ProductSerial> DocDate) )

       

      The final step is to only combine the two expression above to only aggregate the max date from the dates that are within the report period.

       

      Date( Max(TOTAL <ProductSerial> if(DocDate<=ReportEndDate, DocDate)))

       

      With the same approach you can also calculate the sum. See the attached example for details.

       

      To me a set expression based solution looks a bit complicated, and I would really recommend trying to keep the solution as simple as possible. Not only for readability but also for future maintenance. I would consider set expression as a solution if there is a performance issue with using a IF statement, or if the set expression appears easier to grasp than the alternative solution.

        • Re: Only sum Weight for those Product Serials, which has the Maximum Date in the Reporting Month
          Atsushi Saijo

          Dear Toni, I appreciaet for your support in the expression.

           

          *I branched out the discussion.

           

          It turned out to be clear that the logic must be constructed in line with following:

          • If maximum date resides in the reporting month,
          • Maximum Date is: Max(TOTAL <ProductSerial> if(DocDate<=MonthEnd(Today(0),-$(Y)),DocDate));
          • Reporting Month is: Month(Today(0),-$(Y));
          • $(Y) is merely an input value of integer, defining how many months would be shifted from current date;

           

          The maximum date is working.

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

           

          The sum of weight, of which has maximum date in the reporting month, has an logic error:

          • sum ( if(

                    InMonthToDate(

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

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

                      0),Weight))

           

          **I attach the QV file for your inspection, I'd apprecaite for your feedback if any.