Forum Discussion
Frequency Distribution Table Help
I’m attempting to come up with a frequency distribution that enables me to create a visual that shows how many data points meet a specified result. I have two columns in a table named DATE and PRODUCT. I want to be able to visualize the number of DISTINCT PRODUCTS that appear on a COUNT of DATES. E.g., in the provided data set, you'll find that 3 DISTINCT PRODUCTS appear on 5 DISTINCT DATES, and that only 2 PRODUCTS appear on 2 DATES. The background here is that these two fields belong to a table that represents errors. I'll be able to show how many PRODUCTS populate on a given number of dates and will get an idea of how many days it takes an error to get corrected. I also pasted an excel chart of my expected result; note the values are grouped in the axis. Any help is greatly appreciated!
DATE PRODUCT
6/2/2021 A
6/3/2021 A
6/4/2021 A
6/5/2021 A
6/5/2021 A
6/1/2021 B
6/1/2021 B
6/1/2021 B
6/1/2021 B
6/2/2021 B
6/2/2021 B
6/2/2021 B
6/3/2021 B
6/4/2021 B
6/5/2021 B
6/1/2021 C
6/1/2021 C
6/1/2021 C
6/2/2021 C
6/3/2021 C
6/4/2021 C
6/4/2021 C
6/5/2021 C
6/5/2021 C
6/5/2021 C
6/1/2021 D
6/1/2021 D
6/1/2021 D
6/1/2021 D
6/1/2021 D
6/2/2021 D
6/2/2021 D
6/3/2021 D
6/3/2021 D
6/3/2021 D
6/3/2021 D
6/4/2021 D
6/4/2021 D
6/5/2021 D
6/1/2021 E
6/2/2021 E
6/2/2021 E
6/2/2021 E
6/2/2021 E
6/2/2021 E
6/3/2021 E
6/3/2021 E
6/3/2021 E
6/4/2021 E
6/3/2021 F
6/4/2021 F
6/5/2021 F
6/2/2021 E
6/2/2021 E
6/3/2021 E
6/3/2021 E
6/1/2021 G
6/1/2021 G
6/1/2021 G
6/1/2021 G
6/1/2021 G
6/1/2021 G
6/1/2021 G
6/1/2021 G
Hi huntj06,
For your requirement, I think you can create a calculated table based on raw table records to aggregate them based on date value and distinct product count.
All the secrets of SUMMARIZE - SQLBI
After these steps, you can add a calculated column to that table to mapping 'count of product' values to specific number range groups, then you can use the group as axis and 'count of product' as value to create the expected charts.
DC Product Range = IF ( [DC Product] >= 5, "5", IF ( [DC Product] >= 3, "3~4", IF ( [DC Product] >= 1, "1~2" ) ) )Regards,
Xiaoxin Sheng
7 Replies
Hi,
I do not see any chart there? Show the result in a simple Table format with an explanation.
Sorry here is the chart.
I still do not understand how your table is connected to the chart. May be someone else does.
Sorry, let me try this:
The chart is a frequency distribution table with the axis being a binned / grouped set of values; those values are a COUNT of the number of times a PRODUCT is associated with a DATE. E.g, there's one product that is associated with 1 or 2 dates. There's 3 products associated with either 3 - 4 dates or 5 dates.
Hi huntj06,
For your requirement, I think you can create a calculated table based on raw table records to aggregate them based on date value and distinct product count.
All the secrets of SUMMARIZE - SQLBI
After these steps, you can add a calculated column to that table to mapping 'count of product' values to specific number range groups, then you can use the group as axis and 'count of product' as value to create the expected charts.
DC Product Range = IF ( [DC Product] >= 5, "5", IF ( [DC Product] >= 3, "3~4", IF ( [DC Product] >= 1, "1~2" ) ) )Regards,
Xiaoxin Sheng
