4 Replies Latest reply on Dec 23, 2015 11:33 AM by Shinichiro Murakami

    Actions to pass Date and filter by +/-1 week of the date.

    Calvin Wee

      Hi all,

       

      Would request for help in the below Actions filter required :

       

      Report A with Transaction Date and Products.

       

      Report B with Transaction Date, Products and Customers.

       

      When selected Transaction Date and Product on Report A, we would like to who purchase this product and the last 1 week buying trend by passing Transaction Date, Product to Report B.

      Report B will need to have the following filters as :

      1. Filter by Product.

      2. Filter by +/- 1 week transactions of the transaction date passed from Report A.

       

      Any advice will be much appreciated.

       

      Thanks and Regards

        • 1. Re: Actions to pass Date and filter by +/-1 week of the date.
          Shinichiro Murakami

          Calvin,

           

          Seems that Report B has all the required info, then Report A is not needed ??

           

          Put parameter "Transaction Date" with type date.

          Picture1.JPG

          Create Calculated field of

          [Date_Range]

          if [Date]>=([Transactioni Date]-7) and [Date]<=([Transactioni Date]+7) then "show" else "hide" END

          (And Filter by  "show")

          Put [Product ID]'s Quick Filter.

           

          Using Parameter "Transaction Date" and Quick Filter of "Product ID" can bring expected view.

           

           

          Picture1.JPG

           

          Thanks,

          Shin

          • 2. Re: Actions to pass Date and filter by +/-1 week of the date.
            Calvin Wee

            Sorry for not being clearer. I am using it for other tracking but try to use Sales to illustrate which create more confusion.

             

            Here is what is needed :

             

            Report A is the product table with transaction dates.

            When we select on this record, Transaction date will be send to Report B.

            In Report B, which is about the equipment history used to make the part.

            Basing on the Transaction date it will list all the +/-7 days equipment history of the transaction date to see if there were some abnormality happened in the equipment before or after to denote if the product could be affected by this equipment.

             

            e.g. This product is returned. It could be due to the poor Equipment Maintenance done a day before. By listing +/- 7 days of equipment history, we are able to trace if abnormality happened that can be a cause of the failure.

             

            We can also use similar actions to check if products list that the product was produced by the same machine within +/-7 days

             

            Thanks and REgards

            • 3. Re: Actions to pass Date and filter by +/-1 week of the date.
              Shinichiro Murakami

              Calvin,

               

              Little bit struggled to think out, and still not sure this meets the requirement, anyways here is something.

              The difficulty is we cannot have Linked field between table 1 and table 2. (That should be one to multiple)

              Then I combine Prod ID and Trans Date as calc field, then feed them into table 2 using parameter.

              Finally, decompose parameter in table 2.

              Parameter display is not excellent, but still enough to distinguish Prod name/ID.

               

              ==== Solution from here  ====

               

              We have two data table set.

              1.  Trans_date  2.  Event date

               

              1

              2

               

              Create Calculated field in Table1

              [Prod ID , Date]

              [Product ID]+"_"+str([Trans Date])    << *** You can change "_" to whatever which is not used as product name/ID>>

               

              Create Parameter using list in Table 1 - [Prod ID , Date]  (parameter update is manual task each time)

              Show parameter Control

               

              In Table 2 as primary data,

              Create calculated field

              [Transaction Date]

              date(mid([Parameter 1],find([Parameter 1],"_")+1,10))

               

              [Date_Range]

              if [Date]>=[Transaction Date]-7 and [Date]<=[Transaction Date]+7 then "IN" else "OUT" END

               

              Filter by [Date_Range]="IN"

              [Product Name]

              Left([Parameter 1],find([Parameter 1],"_")-1)

               

              [Before/After]

              if [Transaction Date]>[Date] then "Before"

              elseif [Transaction Date]=[Date] then "Exact"

              elseif [Transaction Date]<[Date] then "After" end

               

              ==> Now you can select Product Name from parameter, then table picks up event last 7 days, exact day, next 7 days.

               

              Attached workbook is created by version 9.0

               

              Thanks,

              Shin

              • 4. Re: Actions to pass Date and filter by +/-1 week of the date.
                Shinichiro Murakami

                Calvin,

                 

                I'm interested that I could help your request or not.

                When you have a chance, could you take a look, and let me know if you have any questions.

                 

                Thanks,

                Shin