8 Replies Latest reply: Apr 15, 2014 4:27 AM by Atsushi Saijo Branched to a new discussion. RSS

    The Maximum Date in Month

    Atsushi Saijo

      Dear Gurus,

       

      I have a case where I must select maximum date in a month.

       

      The source table is as such:

      ProductSerialDoc#ActTypeDocDateWeight
      S0001001Issued26.02.20141
      S0001002Cancelled14.03.2014-1
      S0001003Issued18.03.20141
      S0001004Cancelled03.04.2014-1
      S0001005Issued03.04.20141

       

      ProductSerial 'S0001' has 5 associated documents: Doc# {001; 002; 003; 004; 005}.

       

      • Sum all the Weight up to last Month-End, and if it were >0, then take the maximum DocDate with 'Issued' on the last month.

       

      In this case, 18.03.2014 should be selected, and such table should be displayed.

       

      ProductSerialDocDateWeight
      S000118.03.20141

       

      • [Dimention 1] = ProductSerial;
      • [Dimention 2] = FirstSortedDate(DocDate,-DocDate) // does not work;
      • [or Dimention 2] = If(FKDAT<=MonthEnd(Today(0),-1),Max(DocDate)) // does not work;
      • [Expression] = Sum( If( InMonthToDate(DocDate, MonthEnd( Today(0), 0), 0), Weight));

       

      Would you possibly advise how the last DocDate could be displayed?

       

      **This 'maximum date' changes as user selects the targeted reporting month. (In this case, MonthEnd(Today(), -$(X)) ) is used with X as input value.

       

      I'd apprecaite for your feedback.

       

      Atsushi

        • Re: The Maximum Date in Month
          Dariusz Mielczarek

          Atsushi,

           

          i prepared example, which i hope adressess your needs

          For test purposes i prepared few more docs.

           

           

           

           

          Let me know if it is ok and if you like it.

           

          regards

          Darek

          • Re: The Maximum Date in Month
            Dariusz Mielczarek

            Look for comm_maxdateinmonth_thr_505527.qvw


            It works

            • Re: The Maximum Date in Month
              Dariusz Mielczarek

              why not:

              weight sum to the end of closed month:

              if(sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0,sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight))

               

              last issue date to the end of closed month:

              date(if(sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0,FirstSortedValue({<ActType={'Issued'},DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}DocDate,-DocDate)))

               

              ?

              regards

              Darek

               

              where X i value selected by user from field (1,2,3,4,5,....) - it is offset of months.

                • Re: The Maximum Date in Month
                  Dariusz Mielczarek

                  Hi,

                   

                  i should be back in about 2 hours...

                  I try to understand what's changed. I have also to recall my solution for myself It was few days ago i prepared it. From that time i prepared many solutions on this forum So, i can't remember every one.

                   

                  If you need my help. please provide some test data with current and expected result. It will be fastet way for me to understand what to change

                   

                  regards

                  Darek

                  • Re: Re: The Maximum Date in Month
                    Dariusz Mielczarek

                    You need this?:

                    Lokk for attachment. There are 2 new expressions (named new1 and new2).

                    new1:

                    chooses product_serials, for which Weight in "selected" month is positive.

                    {<DocDate={">=$(=MonthStart(Today(0),$(=only(X))))<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0

                     

                    for those serials calculates sum(Weght) from the beginning of that serial history ubtil end of this "selected" month

                    sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)

                     

                    new2:

                    chooses product_serials, for which Weight in "selected" month is positive.

                    {<DocDate={">=$(=MonthStart(Today(0),$(=only(X))))<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)>0

                     

                    for those serials calculates sum(Weght) from the beginning of that serial history ubtil end of this "selected" month

                    sum({<DocDate={"<=$(=MonthEnd(Today(0),$(=only(X))))"}>}Weight)

                     

                    new3 - new criteria to choose possible records to get last issue date:

                    (the same condiotions as in new1 and new2).

                     

                    Let me know if you need more help and if you found correct answer. If yes, you should close thread, to to avoid that people who trying to help will still working on solution for your problem....

                     

                    regards

                    Darek

                      • Re: The Maximum Date in Month
                        Atsushi Saijo

                        Dear Darek, I deeply appreciate for your close support, and we are very close to resoluting it.

                         

                        Having deliberating on it, the issue seems my side: here is the logic construction:

                         

                        //Select only those Product Serial, which has any document date in the targeted month

                        //and then, sum up the weight, only up to the target month.

                         

                        *For example, please look ProductSerial 24551. Initial document is issued on 81.02.2014, and cancelled on 12.03.2014, and re-issued within same date.

                         

                        1. Let's select target month '-1', i.e. March. (Let's see ProductSerial 24551 specifically)
                        2. In this case, the logic must include 24551 because it has DocDate in March
                        3. The logic must sum up only up to MonthEnd() = end of march
                        4. sum +1 -1 +1, resulting in +1.

                         

                        The total must be 818  in the attached QV. If it resulted as 814, it is due to some product serials having +/-1 within the same month, and offseting completely.

                         

                        (previous month) +1

                        (this month)     -1

                        (this monty)     +1

                         

                        Current formula summarize the this-month only, and treat as 0.

                         

                        **attached is the QV file.

                    • Re: The Maximum Date in Month
                      Atsushi Saijo

                      Dear Manish, Darek, and Toni, I appreciate for your fast responses.

                      I have created another thread "Max(TOTAL <>) statement within If statement" based on aggragation, but this thread is valid too. I'd await for your feedback if any. In any case all the input are helpful answers.

                       

                      ****@18:38

                      I have created an AUTONUMBER(MONTH(DocDate)) as FKM#in the loading script:

                       

                      Sum(if(
                      FKDAT_c<=MonthEnd(Today(0),-$(X)),
                      CEIL(AGGR(Weight,ProductSerial,FKM#,ACTTYPE),1)

                       

                          ))

                          -Sum(if(

                      FKDAT_c<=MonthEnd(Today(0),-($(X)+1)),
                      CEIL(AGGR(Weight,ProductSerial,FKM#,ACTTYPE),1)

                          ))

                       

                      This produced 818.

                      ****@09:56

                      Darek has produced following statement, which produced the right result. Further I'd clarify the if(count() ) statement.

                       

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

                      • Re: The Maximum Date in Month
                        Atsushi Saijo

                        Dear Manish and Toni,

                         

                        I appreciate for your input. I branched out the discussion, and could not designate the helpful answer. But all in all, I appreciated your input. The very issue were resolved in two ways: [a.] one way by subtraction and change of weight assignment: [b.] another is to use if (count () ) statement.

                         

                        Very best wishes

                         

                        Atsushi