Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Abhi999
Contributor III
Contributor III

Rolling 6 weeking avg

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.avg.PNG

 

 

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

View solution in original post

11 Replies
chrismarlow
Specialist II
Specialist II

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.

Kushal_Chawda

What if for particular day there is no transaction

chrismarlow
Specialist II
Specialist II

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

Kushal_Chawda

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

Abhi999
Contributor III
Contributor III
Author

not getting expected output

Kushal_Chawda

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

Abhi999
Contributor III
Contributor III
Author

there is no gaps in data

Abhi999
Contributor III
Contributor III
Author

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?

Kushal_Chawda

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