Counting other Records inside the same table

[simoncarts]

Is it possible to Count other records inside the table that I want to display the measure in?

I've built the below table in MS Excel and I need to replicate it in QLIK.

	A	B	C	D	E	F	G
1	Order ID	Account ID	Contract Start Date	Contract End Date	Previous Order Replacement Period Start	Previous Order Replacement Period End	Replacement Orders
2	00001	ABC001	05/05/2020	05/05/2021	30/04/2020	10/05/2020	0
3	00002	ABC002	06/05/2020	06/05/2022	01/05/2020	11/05/2020	0
4	00003	ABC003	07/05/2020	07/05/2023	02/05/2020	12/05/2020	2
5	00004	ABC004	08/05/2020	08/05/2024	03/05/2020	13/05/2020	0
6	00005	ABC005	09/05/2020	09/05/2025	04/05/2020	14/05/2020	0
7	00006	ABC006	10/05/2020	10/05/2026	05/05/2020	15/05/2020	0
8	00007	ABC007	11/05/2020	11/05/2021	06/05/2020	16/05/2020	0
9	00008	ABC008	12/05/2020	12/05/2022	07/05/2020	17/05/2020	0
10	00009	ABC003	04/05/2023	04/05/2026	29/04/2023	09/05/2023	0
11	00010	ABC003	08/05/2023	08/05/2027	03/05/2023	13/05/2023	0

Calculated Fields

• Previous Order Replacement Period Start = Start Date - 5 days
• Previous Order Replacement Period End = Start Date + 5 days

Replacement Orders Calculation:

=COUNTIFS($A:$A,"<>"&A2,$B:$B,$B2,$E:$E,"<="&$D2,$F:$F,">="&$D2) (MS Excel Language)

In English, the calculation reads as...

I want to count the Records where:

• Order ID not equal to Order ID in that particular Row
• Account ID is equal to Account ID in that particular Row
• Contract Start Date is greater than or equal to Previous Order Replacement Period Start in that particular Row
• Contract Start Date is less than or equal to Previous Order Replacement Period End in that particular Row

How do I write the Replacement Orders Calculation in QLIK?

[edwin]

why do rows 10 and 11 not have 2 as the result as well?  when you look at row 10, you will find 2 orders for that account where your rule applies.

[simoncarts]

I'm looking to count it the other way around.

That's why '2' is stated on Row 4 as the orders in Rows 10 & 11 are considered replacements for the order in Row 4 🙂

[Kushal_Chawda]

@simoncarts  what do you mean by OrderID not equal to OrderID in that particular row? What if  OrderID  equal to OrderID in that particular row?

[simoncarts]

I'm looking to count other records. By using the 'not equal to' - it ensures I'm only ever counting other orders 🙂

[Kushal_Chawda]

let's say you have one more order of account ABC003 in between. Let's assume order 0005 in your example has account ABC003. In this case 2 replacement order should be there for both order 00003 and 0005?

[simoncarts]

No as Order ID 00005's contract doesn't end until 2025 so new orders starting in 2023 aren't technically replacing it 🙂

[Chanty4u]

Try this 

Count({<Order ID-={'$(=Order ID)'}, 

        [Account ID]={'$(=Account ID)'}, 

        [Contract Start Date]={"<=$(=Previous Order Replacement Period End)"},

        [Contract End Date]={">=$(=Previous Order Replacement Period Start)"}>} Distinct [Order I

D])