topic Re: Counting other Records inside the same table in App Development

Counting other Records inside the same table

simoncarts — Fri, 05 May 2023 16:23:05 GMT

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?

Re: Counting other Records inside the same table

edwin — Fri, 05 May 2023 18:22:09 GMT

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.

Re: Counting other Records inside the same table

simoncarts — Fri, 05 May 2023 18:44:00 GMT

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 🙂

Re: Counting other Records inside the same table

Kushal_Chawda — Fri, 05 May 2023 20:49:11 GMT

@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?

Re: Counting other Records inside the same table

simoncarts — Fri, 05 May 2023 21:14:57 GMT

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

Re: Counting other Records inside the same table

Kushal_Chawda — Fri, 05 May 2023 22:26:16 GMT

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?

Re: Counting other Records inside the same table

simoncarts — Sat, 06 May 2023 08:18:19 GMT

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

Re: Counting other Records inside the same table

Chanty4u — Sat, 06 May 2023 12:53:31 GMT

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])