9 Replies Latest reply on Oct 29, 2018 12:04 AM by David Gabra

    Travel Dates

    David Gabra

      Hi

       

      Please refer to the attached example file.

      I would like to know any employee (I am sure we need the 'fixed' calculated field) who submitted a tax receipt where the cost is above 50,  - here is the complex part, this is were the expense within 1 day of the trip start date.

      Conditions - to show "yes"

      1. only is schema name is business

      2. only if the receipt date is within 1 day of the 'trip begin on date' and Travel Expense Type is 'taxi' and greater than '50'

      3. Per employee and trip  (must have these 2)-   I tried fix on both did not work.

      4. Can we also state how much greater Loc.currency amount than 50 - if the answer is 'yes'.

         

           

      Personnel NumberTrip NumberSchema nameTrip Begins OnReceipt DateLoc.currency amountTravel Expense TypeInvestigate - calc field
      (with 1 day and abovce 50)
      Difference  -calc field
      15612121Business Trip22-May-1822-May-18444taxiYes 384
      15612121Business Trip22-May-1825-May-18444taxino-
      189342323Cancel23-Jun-1823-Jun-18456taxino-
      123234234Business Trip24-Aug-1824-Sep-18343taxino-

       

      Many thanks

      David.

        • 1. Re: Travel Dates
          Don Wise

          Hello David,

          See if the attached 2018.2 workbook and below screenshot work? If yes, please mark as correct. Thx! Don

          Screen Shot 2018-10-28 at 2.28.38 PM.png

          1 of 1 people found this helpful
          • 2. Re: Travel Dates
            Peter Fakan

            Hi David,

             

            Like this ?

             

             

            HTH 18.2 attached

             

            Peter

            1 of 1 people found this helpful
            • 3. Re: Travel Dates
              David Gabra

              Hi

              [Receipt Date]=DATEADD('day',0,[Trip Begins On])

               

              just to lean - the 0 means on the same day and if this was '2' then it would refer to 2 days after the receipt date  - is this correct?

               

              if we wanted to to one day before - what would I write? is there dateminus

               

              I am so keen to learn

               

              thanks a million.

               

              thank you

               

              David.

              • 4. Re: Travel Dates
                David Gabra

                Hi

                Can you explain the date difference

                - why did you add Monday

                - why smaller that 1

                I want to learn.

                thank you

                • 5. Re: Travel Dates
                  David Gabra

                  +

                   

                  1.Can you also explain why you did not use - {fixed] if I need this per employee and per trip

                   

                  1. What would be the calculated filed if I wanted to calculate per trip per employee the number of taxi receipts that were charged.?

                   

                  thank you

                   

                  David.

                  • 6. Re: Travel Dates
                    David Gabra

                    + in addition to the prior question

                     

                    +

                     

                    1.Can you also explain why you did not use - {fixed] if I need this per employee and per trip  (now I think about - not relevant - but will be for the question below.

                     

                    1. What would be the calculated filed if I wanted to calculate per trip per employee the number of taxi receipts that were charged.?

                     

                    thank you

                     

                    David.b

                    • 7. Re: Travel Dates
                      Peter Fakan

                      Hi David,

                       

                      1. The DateDiff formula is DATEDIFF(date_part, start_date, end_date, [start_of_week]) - the Monday was just filling the start of week box.

                       

                      Then the <1 is the 'only if the receipt date is within 1 day of the 'trip begin date' - because the start and end date are contained inside the datediff statement, I wanted to capture the same day (i.e. if the trip and the receipt were on the same day) and the next day.

                       

                      2. I didn't use fixed because I had all of the required fields out on the canvas - didn't feel I needed the complexity.

                       

                      3. err - dunno. I'd probably just use fixed statements with nothing on the left side of the colon but that would be a different use-case.

                       

                      HTH

                       

                      Peter

                      • 8. Re: Travel Dates
                        David Gabra

                        1. What would be the calculated filed if I wanted to calculate per trip per employee the number of taxi receipts that were charged.?

                        • 9. Re: Travel Dates
                          David Gabra

                          Hi

                          I will ask in a new thread to not confuse.

                          thanks for everything.