Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kostas_livanos
Contributor
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
kostas_livanos
Contributor
Contributor
Author

swuehl‌ do you have any idea?