Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have an issue with calculation between two periods.I have below data set and i want to calculate values per CustomerID where Authorization Date >= Previous Max Order Date and Authorization Date <= Max Order Date.
CustomerID | Total Quantity per CustomerID Between Previous Max Order Date and Max Order Date | Supported Transactions (Total Quantity *50) | Max Order Date | Previous Max Order Date | Authorization Date | Transactions |
---|---|---|---|---|---|---|
00001 | 36(Same where CustomerID 0001) | 36*50=1800 | 06/09/2018 | 15/06/2018 | 30/04/2018 | 1 |
00001 | 36(Same where CustomerID 0001) | 1800 | 06/09/2018 | 15/06/2018 | 22/01/2018 | 1 |
00001 | 36(Same where CustomerID 0001) | 1800 | 06/09/2018 | 15/06/2018 | 28/06/2018 | 1 |
00002 | 24(Same where CustomerID 0002) | 1200 | 23/07/2018 | 18/06/2018 | 10/07/2018 | 1 |
00002 | 24(Same where CustomerID 0002) | 1200 | 23/07/2018 | 18/06/2018 | 15/06/2018 | 1 |
I should have the result:
MerchantAFM,TransactionsBetweenTwoDates
094009878,1
094186729,1
I tried to calculate with two ways but i had result either because reload failed after 6 hours.
Each MerchantAFM has a lot of transactions for each AuthorizationDay.
1st way:
Comparison:
Load [Merchant AFM],
IF(AuthorisationDate>= PreviousMaxOrderDate and AuthorisationDate<= MaxOrderDate,sum(Transactions),'No Transactions') as TransactionsBetween
resident Between;
2nd way:
Load [Merchant AFM],
sum(Transactions) as TransactionsBetween
resident Between where AuthorisationDate>= PreviousMaxOrderDate and AuthorisationDate<= MaxOrderDate group by [Merchant AFM];
Is there any another way to calculate this problem?Either with set analysis either with dates variables of Max order date and Previous Max Order Date.Is there any format problem?
Script is attached.
swuehl do you have any idea?