Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculation between two periods

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.

CustomerIDTotal Quantity per CustomerID Between Previous Max Order Date  and Max Order DateSupported Transactions (Total Quantity *50)
Max Order DatePrevious Max Order DateAuthorization DateTransactions
0000136(Same where CustomerID 0001)36*50=180006/09/201815/06/201830/04/20181
0000136(Same where CustomerID 0001)180006/09/201815/06/201822/01/20181
0000136(Same where CustomerID 0001)180006/09/201815/06/201828/06/20181
0000224(Same where CustomerID 0002)120023/07/201818/06/201810/07/20181
0000224(Same where CustomerID 0002)120023/07/201818/06/201815/06/20181

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.

1 Reply
Anonymous
Not applicable
Author

swuehl‌ do you have any idea?