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: 
mrainey18
Creator
Creator

Set Analysis

I am trying to come up with a way to do the 90 day average for each weekday.  This works for the 90 day average but I'm not sure how I am going to work in the by weekday.  Any ideas?

avg( {<CalendarDate = {"$(='>' & $(v90Days))"}>} Sales)

9 Replies
sunny_talwar

May be create a weekday flag in the script....

If(Match(WeekDay(CalendarDate), 'Mon', 'Tue', 'Wed', 'Thu', 'Fri'), 1, 0) as WeekDayFlag

and then this

Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDayFlag = {1}>} Sales)

mrainey18
Creator
Creator
Author

Its not just weekdays.  I'm trying to get the average sales of Mondays for the past 90 days and compare it to Yesterday average sales.  Same with Tuesday, Wednesday, ect.

So if I have a sales person list of sales for the past year I want to be able to compare each Monday to the past 90 days of Mondays.  Same thing with the rest of the days of the week.

The only thing I came up with is doing some funky stuff on the back end that compares multiples of 7 for the past 7 days while today()-CalendarDate < 91, then I mapped it to the data set.  That adds 10 mins to the load time and I was trying to come up with another way.

sunny_talwar

What is the kind of chart you are using? Do you have a sample or images to show what you are after?

dwforest
Specialist II
Specialist II

Extending Sunny's suggestion, create another field in your Calendar that contains the Day,

WeekDay(CalendarDate) As WeekDay


and then this

Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDay = {'Mon'}>} Sales)

mrainey18
Creator
Creator
Author

I am using a Pivot Table.  Rows are Team, Account Executive, Date.

Unfortunately I can not give you a screen shot because of the customer data.

mrainey18
Creator
Creator
Author

This would work if I was only comparing Mondays, but I'm comparing Yesterday or all the days in the last Quarter to the 90 day average.

We have high performing days and low performing days and its not fair to compare a high performing day to a low performing day and the average of the two doesn't give us much of a comparison either.  So comparing each day to itself is what we are looking for but its proving difficult to figure out.

dwforest
Specialist II
Specialist II

Not sure exactly what you are trying to do, but the expression could be altered to

Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDay = {$(WeekDay(Today()-1)}>} Sales)


to have it evaluate what day yesterday was. Of course this all depends on other current selections.

Other solutions could involve putting the WeekDay field in a Filter box to allow selection of the actual day...

To receive additional feedback it is helpful to supply example data with the expected outcome.

mrainey18
Creator
Creator
Author

Final goal is to be able to look at past 30 to 90 days worth of sales per AE() per Customer and compare those days to the Average over the past 90 days.  I'll be displaying it in a Pivot table and if the sales are down 10% or 25% then the Cell will be highlighted.  Then they can look into that day to see where the AE can improve.

Most of the time they are looking at yesterday, but they could be looking at the past 90 days or even more if they wanted too. 

I am trying to come up with a way to display the average 90 day per day of the week.  Does that make sense?

dwforest
Specialist II
Specialist II

Not entirely... sample data to illustrate what you are trying to do with expected output/visualization is always helpful