I have a table with COVID information from my campus. My ultimate goal is to create a line chart with three series/lines charted over several days.
A button creates a collection (colChart) with two columns: open_file_date and reason_code (CASE, PROBABLE, and CONTACT), and filters the collection by a custom number of days. In the chart’s Items, I use AddColumns, GroupBy, Filter, and CountRows for one of the Reason Codes to get a count of the rows for each date with one Reason Code and get it to draw a series line. I would like three separate series/lines, one for each of the three possible Reason Codes so I need a count of all three possibilities in reason_code for each date.
Line Chart Items: AddColumns( GroupBy( Filter(colChart,reason_code="CASE"), "open_file_date","CASE_DATA"), "CaseCount", CountRows(CASE_DATA))
I know that AddColumns can add multiple columns to get PROBABLE and CONTACT’s counts, but can I do that in the above AddColumns statement, or is there another, better way? I've just about exhausted the YouTube videos on this one. Thanks!
