Hi all, I'm trying to calculate the Distinct Count of Invoices in my data for each year (2022, 2023) but can't figure it out. Any and all help would be greatly appreciated!

Here's a sample of the data I'm using:

Type-   Invoice #- Product- Date-   Qty-   Amount
Invoice       15       A   10/1/2022   100   2000
Invoice       15       B   11/1/2022   8   160
Invoice       16       A   11/2/2022   25   500
Invoice       16       B   11/2/2022   4   80
Invoice       16       C   11/2/2022   3   60
Invoice       17       D   12/1/2023   18   360
Invoice       18       E   12/2/2023   20   400
Invoice       19       F   9/3/2023           7   140
Invoice       20       G   8/4/2023           91   1820
Invoice       20       G   8/4/2023           24   480
Invoice       21       H   12/5/2023   13   260
Order       1               Z   12/2/2023   11   220
Order       2               Y   12/2/2023   88   1760
Order       3               X   12/3/2023   9   180
Order       4              W   12/1/2023   52   1040
Order       4              U   12/1/2023   41   820
Order       5              V   12/4/2023   33   660
               

Below is what I would ideally like to be able to determine:
# of unique invoices by year and by type (Invoice only, not Order), and the variance/change in # of unique invoices between the years.            
               
Distinct Count of Invoices   Variance      
2022   2023            
2              5                        150%   
   

Quote from: kw on 28 Dec 2023 03:29:40 PMHi all, I'm trying to calculate the Distinct Count of Invoices in my data for each year (2022, 2023) but can't figure it out. Any and all help would be greatly appreciated!

Here's a sample of the data I'm using:

Type-   Invoice #- Product- Date-   Qty-   Amount
Invoice       15       A   10/1/2022   100   2000
Invoice       15       B   11/1/2022   8   160
Invoice       16       A   11/2/2022   25   500
Invoice       16       B   11/2/2022   4   80
Invoice       16       C   11/2/2022   3   60
Invoice       17       D   12/1/2023   18   360
Invoice       18       E   12/2/2023   20   400
Invoice       19       F   9/3/2023           7   140
Invoice       20       G   8/4/2023           91   1820
Invoice       20       G   8/4/2023           24   480
Invoice       21       H   12/5/2023   13   260
Order       1               Z   12/2/2023   11   220
Order       2               Y   12/2/2023   88   1760
Order       3               X   12/3/2023   9   180
Order       4              W   12/1/2023   52   1040
Order       4              U   12/1/2023   41   820
Order       5              V   12/4/2023   33   660
               

Below is what I would ideally like to be able to determine:
# of unique invoices by year and by type (Invoice only, not Order), and the variance/change in # of unique invoices between the years.            
               
Distinct Count of Invoices   Variance      
2022   2023            
2              5                        150%   
   

Hi,

You could use the following approach:
1 - create calculations to isolate the invoice numbers for 2022 and 2023, eg

2022 Invoice #s: if ([Type] = 'Invoice' and extract(year, [Date]) = 2022) then ([Invoice #]) else (null)
2023 Invoice #s: if ([Type] = 'Invoice' and extract(year, [Date]) = 2023) then ([Invoice #]) else (null)

You could then count these:

2022 Invoices: count ([2022 Invoice #s] for report)
2023 Invoices: count ([2022 Invoice #s] for report)

The variance would then be: [2023 Invoices] / [2022 Invoices] * 100

Cheers!

MF.

Thank you MF!

I didn't think to isolate them beforehand and once I figured out to change the usage, I got it all to work.

Thank you very much for your help!

Quote from: kw on 28 Dec 2023 03:29:40 PMHi all, I'm trying to calculate the Distinct Count of Invoices in my data for each year (2022, 2023) but can't figure it out. Any and all help would be greatly appreciated!

Here's a sample of the data I'm using:

Type-   Invoice #- Product- Date-   Qty-   Amount
Invoice       15       A   10/1/2022   100   2000
Invoice       15       B   11/1/2022   8   160
Invoice       16       A   11/2/2022   25   500
Invoice       16       B   11/2/2022   4   80
Invoice       16       C   11/2/2022   3   60
Invoice       17       D   12/1/2023   18   360
Invoice       18       E   12/2/2023   20   400
Invoice       19       F   9/3/2023           7   140
Invoice       20       G   8/4/2023           91   1820
Invoice       20       G   8/4/2023           24   480
Invoice       21       H   12/5/2023   13   260
Order       1               Z   12/2/2023   11   220
Order       2               Y   12/2/2023   88   1760
Order       3               X   12/3/2023   9   180
Order       4              W   12/1/2023   52   1040
Order       4              U   12/1/2023   41   820
Order       5              V   12/4/2023   33   660
               

Below is what I would ideally like to be able to determine:
# of unique invoices by year and by type (Invoice only, not Order), and the variance/change in # of unique invoices between the years.            
               
Distinct Count of Invoices   Variance      
2022   2023            
2              5                        150%   
   

I addition to what MFGF suggested, I have used this method also:

1> Invoice_2022 :
     count (distinct case when [Type] = 'Invoice' and extract(year,[Date]) = 2022 then [Invoice #] end )

2> Invoice_2023 :
     count (distinct case when [Type] = 'Invoice' and extract(year,[Date]) = 2023 then [Invoice #] end )

Variance = ( [Invoice_2023] / [Invoice_2022] )  * 100

In newer version (11.2 onwards), use this:

case when [Type] = 'Invoice' and extract(year,[Date]) = 2022 then [Invoice #] end

case when [Type] = 'Invoice' and extract(year,[Date]) = 2023 then [Invoice #] end

In the aggregation property, select 'count distinct' .