Hello,
I have a dataset with fields id, bill_date, company along with other fields. I need to calculate 7-day and 30-day moving average of the count of records for each bill_date and break it down by company. I have a table visual, with company as rows, bill_date as columns. I need to show three values: total count of records, 7-day moving average and 30-day moving average for each day. There are filters for bill_date and company, so user can filter the data as needed.
I am using below calculated expression for getting moving average:
windowAvg(count({id}), [{bill_date} ASC],30,0,[{company}]).
Issue I am facing is: if user filters for date range that spans 15 days, the 30-day moving average is not showing correct values, as the windowAvg calculates the average on the filtered data rather than pre-filter data.
I have gone thru the related posts in this community, but they have a requirement to show specific number of data points, whereas in my case I do not know up front on how many data points to show, as the user can filter the data for the desired date range.
If user selects only one day, both the total count and moving averages are showing the same values, which is incorrect.
Appreciate any help.
Hi @kborra, you might want to try out the date hider trick in the Demo Central. By using the calculated filed minOver(min({order date}), [{order date}]) as a target of your filter instead of the original date field, the filter will just apply to the visual level not at the underlying data level.
Thank you @ytakahr . I tried the suggestion. It worked great. Thank you so much.
1 Like