Calculation between two periods

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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)	3650=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.

Re: Calculation between two periods

Anonymous — Tue, 23 Oct 2018 07:08:54 GMT

swuehl‌ do you have any idea?

topic Re: Calculation between two periods in QlikView

Calculation between two periods

Re: Calculation between two periods