Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
What if for particular day there is no transaction
Agreed it is a fragile solution that depends heavily on data model, presence of data & sorting ☹️
@chrismarlow Your solution will work fine if there is no data gaps. That is why I asked question to @Abhi999
not getting expected output
Can you answer my question in previous reply? Also share some sample data with expected output?
there is no gaps in data
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?
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