topic Rolling 6 weeking avg in App Development

Rolling 6 weeking avg

Abhi999 — Fri, 07 Aug 2020 16:02:45 GMT

I Am try to show trend in combo chart. trend line is last 6 weeks avg. for example today Friday I want to show last 6 Fridays avg, for Thursday last 6 Thursdays like that. last 6 weeks same day avg.

Re: Rolling 6 weeking avg

chrismarlow — Sat, 08 Aug 2020 13:51:27 GMT

Hi,

There may be a more elegant solution but you could use above function. There is some more detail here on getting above to work with date selection which explains the Avg(1) (thanks @sunny_talwar ). So something like;

(above(Sum({<[Date]>}Value),7)+above(Sum({<[Date]>}Value),14)+above(Sum({<[Date]>}Value),21)+above(Sum({<[Date]>}Value),28)+above(Sum({<[Date]>}Value),35)+above(Sum({<[Date]>}Value),42))*Avg(1)/6

Cheers,

Chris.

Re: Rolling 6 weeking avg

Kushal_Chawda — Sun, 09 Aug 2020 12:22:33 GMT

What if for particular day there is no transaction

Re: Rolling 6 weeking avg

chrismarlow — Sun, 09 Aug 2020 15:19:19 GMT

Agreed it is a fragile solution that depends heavily on data model, presence of data & sorting ☹️

Re: Rolling 6 weeking avg

Kushal_Chawda — Sun, 09 Aug 2020 19:00:58 GMT

@chrismarlow Your solution will work fine if there is no data gaps. That is why I asked question to @Abhi999

Re: Rolling 6 weeking avg

Abhi999 — Mon, 10 Aug 2020 13:24:15 GMT

not getting expected output

Re: Rolling 6 weeking avg

Kushal_Chawda — Mon, 10 Aug 2020 13:26:19 GMT

Can you answer my question in previous reply? Also share some sample data with expected output?

Re: Rolling 6 weeking avg

Abhi999 — Mon, 10 Aug 2020 13:28:58 GMT

there is no gaps in data

Re: Rolling 6 weeking avg

Abhi999 — Mon, 10 Aug 2020 13:32:23 GMT

I have a simple bar chart that displays the sum of sales by day. I would like to add, in the same chart, a line for the average value for each day of the week (average sales for Mondays, Tuesdays, ...). I am able to get the average values in a separate listbox (or chart) having the dayOfWeek as dimension, but can't find a way to add them to the daily sales chart for last 6 weeks

Is there a way to do that?

Re: Rolling 6 weeking avg

Kushal_Chawda — Mon, 10 Aug 2020 13:46:28 GMT

Taking day of the week as dimension won't give you correct avg as one day could be there in more than 6 weeks. For quicker and correct reply you can share sample data with expected output

Re: Rolling 6 weeking avg

Abhi999 — Tue, 11 Aug 2020 05:45:07 GMT

I hope I explained clearly in initial question, manually I preparing data is tough. I will try to explain my expected output.

In combo chart bars are actuals every day sum, trend line I want show last 6 weeks same day (if today is Tuesday last 6 Tuesdays avg , Mondays like that ) avg. in initial question I have posted sample combo chart

Re: Rolling 6 weeking avg

Abhi999 — Tue, 11 Aug 2020 07:32:32 GMT

Many thanks it works fine.