2 Replies Latest reply on Oct 15, 2018 5:10 AM by Kostas Livanos

    Calculation error between two dates

    Kostas Livanos

      Hi Everyone,


      I have a requirement that orders and transactions should be compared and a final list should be showed after some calculation.I want to calculate the number of transactions after second max order date for each Merchant AFM.The first Data Set(tickets) includes(Ticket ID,Order Date,Quantity(Qty or Type),MerchantID) ,second Data Set(Merchants) includes (MerchantID,MerchantAFM)  and third Data Set(Transactions) includes(MerchantAFM,Transaction Date,sum of Transactions)



      1)  Inner join with MerchantID of two first Data Sets

      2) Calculation of Max Order Date,Previous Max Order Date and Total Quantity per MerchantAFM ,Quantity between of last two Order Date

      3)When i add Data Set (Transactions)

      and join with MerchantAFM(either left join or inner join) i have duplicates Tickets,Order Dates and Quantity because i have different Authorizations Date for each Merchant.

      So it affects quantities.When i add transactions data set as a new data source and connect two data sets with relationship (Merchant AFM), I can not calculate the  total number of Transactions when Authorization Date > second max order date.

      I receive this error --All fields must be aggregate or constant when using Table Calculations or fields from multiple data sources--

        • 1. Re: Calculation error between two dates
          Tim Dines

          Based on the error message, you need to wrap each of your non-aggregate fields in the calculated field in ATTR(), MAX(), or MIN().  If you shared the calculated field that gives the error message, there are any number of people on here that can help.

          • 2. Re: Calculation error between two dates
            Kostas Livanos

            Thank you for your reply. i have numbers of transactions with relationship and this calculation :


            IF ATTR([Transactions2].[Authorisation Date])>=ATTR([Second Max Date per AFM])  and ATTR([Transactions2].[Authorisation Date])<=ATTR([Max Order Date per AFM]) THEN SUM([Transactions2].[Transactions]) END 


            *14 September Second Max Date per AFM

              20 Max Order Date per AFM





            but if removed authorisation DAY from rows sum of transactions for each MerchantAFM is null.



            Calculating Sum of Transactions between last two dates  per Merchant AFM i receive this error


            Removing sum