15. Unique values filtered by date


=UNIQUE(2.FILTER(SORT1.(FILTER(SOURCE[[BATCH]:[DATE]];(SOURCE[BATCH]<>"")*(SOURCE[PROCEDURE]=17)*(SOURCE[BATCH]>=1));{1\2};{1\1});{1\0}))

    This formula is a nested UNIQUE, FILTER, and SORT function that returns an array of unique values from the range BATCH:DATE of the table SOURCE where the value in the column BATCH is not empty, the value in the column PROCEDURE is equal to 17, and the value in the column BATCH is greater than or equal to 1. The array is sorted in ascending order by the values in the second column of the range BATCH:DATE. The first column of the array is returned.

    Here’s how it works:

  1.  FILTER(SOURCE[[BATCH]:[DATE]];...) returns an array of values where each value is a cell in the range BATCH:DATE of the table SOURCE where the value in the column BATCH is not empty, the value in the column PROCEDURE is equal to 17, and the value in the column BATCH is greater than or equal to 1.

  2.  SORT(...;{1\2};{1\1}) sorts the array returned by step 1 in ascending order by the values in the second column of the range BATCH:DATE.

  3.  UNIQUE(...;{1\0}) returns an array of unique values from the array returned by step 2 where only the first column of the range BATCH:DATE is returned.

SORT

Sort - Syntax

SORT(array, [sort_index], [sort_order],[by_col])

Sorts the result in an ascending manner when you apply "1" on [sort_index].

At our table:

Sort
2
2
2
6
FILTER

Filter - Syntax

FILTER (array, include, [if_empty])

Filter was used twice. See 1 and 2 above.

At our table:

First filter
614/07/2022
220/06/2022
215/06/2022
216/06/2022

At our table:

Second filter
2
2
2
6
UNIQUE

Unique - Syntax

UNIQUE (array, [by_col], [occurs_once])

Returns unique values

At our table:

Unique Values
2
6