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' .