Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
kostas_livanos
New Contributor

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
Highlighted
kostas_livanos
New Contributor

Re: Calculation between two periods

swuehl‌ do you have any idea?