I have a dataset in which I need to calculate the monthly average using the daily counts. So I need to include both the field for the date, and the month (I am not a developer so can’t change these options)
So after a complicated calculation, I have yielded the right results, but I only want a to view monthly, but there are as many rows as are days. Is there any way to fix this visualization?
The data set looks like this:
The visualization is reflecting this:
Rather than the number of rows equivalent to days, I just want 12 rows to display the months, if that makes sense. Any ideas?
Not sure if this will help, but could you add a Measure/Calculation for the SUM, and one for the COUNT, then do a calculation for this Average?
Then you can hide the SUM and COUNT from the Visualisation, and remove the Date column entirely
Unfortunately that won’t work as the count field will give me the count of vehicles, and I actually need just the count of days in the given month that yield a result (hence the formula I created).
I think we could accomplish this (only in the visualization, though) by using a clever little feature called “Hide No’s from Visualization”.
Basically, if you built a table calc that did some kind of check to see if the row above it in the Dt Month column was different, and only returned “Yes” if the row above WAS different, you would have a column that said Yes once per month and No for the remaining results.
Then, if you click into that calculated field’s options and choose both “Hide from Visualization” & “Hide No’s from Visualization”
You get the grouping you want, while only returning the few rows you desire.
I’ll let you noodle on how you could build that table calculation 😉 let me know if you get stuck, happy to help!
Also, if you wanted to do this without the visualization options / in a cleaner way, this’d be a pretty good place to use a derived table with a window function to perform the calculation over the relevant rows and return just 1 row per month.