13 Replies Latest reply on Jan 24, 2019 5:44 AM by Kalyan Raman

    Time Difference

    Kalyan Raman

          IF [final_order_state]='filled' AND [unfilled_amount]<=[order_amount]
        THEN  (DATEDIFF('second',MAX([event_time]),MIN([event_time])))

       

        ELSEIF [final_order_state]='cancelled' AND [order_amount]=[unfilled_amount]
        THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

       

        ELSEIF [final_order_state]='cancelled' AND [unfilled_amount]!=[order_amount]
        THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

       

        END

       

      ERROR: "can not mix aggregate and non-aggregate comparisons or results in 'IF' expressions.

       

      How can I correct it.

        • 1. Re: Time Difference
          Jim Dehner

          we cant see the data or the other calculations but whenever you get that message it means some of the measures/dimensions in your formula are aggregated (like MAX()  or MIN() )  and some are not like Final_order_staet"

          you need to aggregate them and can use attr() if they are strings - you also have measures like unfilled amount and order amount - if they are not already aggregates then they would need to be aggregated with sum()

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Time Difference
            Kalyan Raman

            Jim,

             

            I ve tried but with no luck.

             

              IF [final_order_state]=attr('filled') AND [unfilled_amount]<=[order_amount]
              THEN  (DATEDIFF('second',MAX([event_time]),MIN([event_time])))

             

              ELSEIF [final_order_state]=attr('cancelled') AND [order_amount]=[unfilled_amount]
              THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

             

              ELSEIF [final_order_state]=attr('cancelled') AND [unfilled_amount]!=[order_amount]
              THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

             

              END

            • 3. Re: Time Difference
              Kalyan Raman

              Jim,

               

              I ve tried but with no luck.

               

                IF [final_order_state]=attr('filled') AND [unfilled_amount]<=[order_amount]
                THEN  (DATEDIFF('second',MAX([event_time]),MIN([event_time])))

               

                ELSEIF [final_order_state]=attr('cancelled') AND [order_amount]=[unfilled_amount]
                THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

               

                ELSEIF [final_order_state]=attr('cancelled') AND [unfilled_amount]!=[order_amount]
                THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

               

                END

               

              final_order_state is a string

              unfilled_amount & order_amount are integers

              event_time is a time

              1 of 1 people found this helpful
              • 4. Re: Time Difference
                Prasanna R

                Hi Kalyan,

                 

                you should use ATTR to the dimension.. not to the value. Please let me know if this helps.

                 

                IF ATTR([final_order_state])='filled' AND SUM([unfilled_amount])<=SUM([order_amount])

                THEN  (DATEDIFF('second',MAX([event_time]),MIN([event_time])))

                 

                ELSEIF ATTR([final_order_state])='cancelled' AND SUM([order_amount])=SUM([unfilled_amount])

                THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

                 

                ELSEIF ATTR([final_order_state])='cancelled' AND SUM([unfilled_amount])!=SUM([order_amount])

                THEN DATEDIFF('second',MAX([event_time]),MIN([event_time])))

                 

                END

                1 of 1 people found this helpful
                • 5. Re: Time Difference
                  Kalyan Raman

                  Prasanna,

                   

                  I still get "EXPECTED ENF TO MATCH IF at character 0"

                  • 6. Re: Time Difference
                    Prasanna R

                    You might have extra brackets.. try this

                     

                    IF ATTR([final_order_state])='filled' AND SUM([unfilled_amount]) <= SUM([order_amount])

                    THEN  DATEDIFF ('second', MAX([event_time]), MIN([event_time]))

                     

                    ELSEIF ATTR([final_order_state])='cancelled' AND SUM([order_amount])=SUM([unfilled_amount])

                    THEN DATEDIFF('second',MAX([event_time]),MIN([event_time]))

                     

                    ELSEIF ATTR([final_order_state])='cancelled' AND SUM([unfilled_amount])!=SUM([order_amount])

                    THEN DATEDIFF('second',MAX([event_time]),MIN([event_time]))

                     

                    END

                    1 of 1 people found this helpful
                    • 7. Re: Time Difference
                      Kalyan Raman

                      Thanks Prasanna,

                       

                      That's working.

                      • 8. Re: Time Difference
                        Prasanna R

                        Please mark it as Answered so that others can refer this.. Appreciate it..

                        • 9. Re: Time Difference
                          Kalyan Raman

                          Prasanna,

                          How would I calculate the time difference here. They both belong to the same order ID.

                          In the formula I used Max and Min to find out the difference of the times.

                          Does that work

                           

                           

                          Cancelled22:52.1
                          Cancelled13:22.4
                          • 10. Re: Time Difference
                            Prasanna R

                            Kalyan, Can you attach a small dummy data set of how it is currently so that I can help you better.

                            • 11. Re: Time Difference
                              Kalyan Raman

                              Prasanna,

                               

                               

                               

                               

                               

                               

                              This is a dummy date similar to what Im looking for. The order ID is same, hence I have to calculate the difference of Cancel and Creation time differences in event_time-1

                               

                               

                               

                               

                               

                               

                               

                              order_idinstrumentbuy_sellpriceorder_amountunfilled_amountfinal_order_stateorder_amount-1unfilled_amount-1final_order_state-1event_time-1event_type-1
                              1222GBPUSDS1.26870000007000000Cancelled70000007000000Cancelled13/12/18 16:53Creation
                              1222GBPUSDS1.26870000007000000Cancelled70000007000000Cancelled13/12/18 21:26Cancel
                              • 12. Re: Time Difference
                                Prasanna R

                                Ok great.. so to get more clarity..

                                Looks like we need to do some tweaking to the calculation.

                                 

                                There are many Order IDs in your data set?

                                You will have only 2 entries for each Order ID? One line for created and one for Cancelled OR one line for created and one for filled?

                                There will never be a scenario where you can have Created, Filled and Cancelled?

                                 

                                If I were to describe our goal - it is to find the time taken for an order ID to move from Creation to Cancelled / filled state.. Correct?

                                1 of 1 people found this helpful
                                • 13. Re: Time Difference
                                  Kalyan Raman

                                  Many Order ID's - YES

                                  Entries can be 1 or 2 or 3

                                  True

                                   

                                  Correct