
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Auto Calendar - Past 7 Days
Hi All,
I am trying to determine the average Sale amount for employees over the past 7 Days. And this would be the past 7 Days that they had a Sale. This is indicated by the count of the Sale_RecordNum being greater than 0 in the dataset. Right now it is just looking at the dataset and providing me with the average sale for all the days that the employee had a sale. I would like to just look at the past 7 days that on which they had a sale occur. Or maybe, just limit the autocalander to only filter its count of the sales number and the count of the Sales records to the past 7 Days.
Current Expression:
((Sum(Sale_Ammount))/count( distinct if( Sale_RecordNum > 0 , [Sales.SALE_DT.autoCalendar.Date])))
Thanks in advance!
- Tags:
- sum
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would create a new field in the load script that ranks each employee's work days from most recent (1) back in time and then use that in set analysis. (Alternatively, you can do with Aggr, but I find that tricky to get right.)
((Sum({<Sale_RecordNum={"> 0"}, EmpWorkDayRank={"<=7"} >} Sale_Ammount))/count({<Sale_RecordNum={"> 0"}, EmpWorkDayRank={"<=7"} >} distinct [Sales.SALE_DT.autoCalendar.Date]))
The load script might be something like:
RankedData:
Load
EmployeeID,
Sales.SALE_DT,
If(EmployeeID=Previous(EmployeeID), Peek(EmpWorkDayRank)+1, EmpWorkDayRank) as EmpWorkDayRank
;
Load
EmployeeID,
Sales.SALE_DT,
1 as EmpWorkDayRank
Resident SalesData
Order By EmployeeID, Sales.SALE_DT desc;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
When you say "past 7 days" do you mean looking back one week from today, or any 7 days in the past on which the employee had a sale, even a year ago?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Preferably, the past 7 days that the employee had a sale. So that could go back a month or further if they have had a leave of absence. Really, I am looking to smooth out results to get a 7 day view. The past 7 days, could work, but if I look at the past 7 worked days, then I think it would provide a better view of the performance. To care for days off, vacations, etc.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would create a new field in the load script that ranks each employee's work days from most recent (1) back in time and then use that in set analysis. (Alternatively, you can do with Aggr, but I find that tricky to get right.)
((Sum({<Sale_RecordNum={"> 0"}, EmpWorkDayRank={"<=7"} >} Sale_Ammount))/count({<Sale_RecordNum={"> 0"}, EmpWorkDayRank={"<=7"} >} distinct [Sales.SALE_DT.autoCalendar.Date]))
The load script might be something like:
RankedData:
Load
EmployeeID,
Sales.SALE_DT,
If(EmployeeID=Previous(EmployeeID), Peek(EmpWorkDayRank)+1, EmpWorkDayRank) as EmpWorkDayRank
;
Load
EmployeeID,
Sales.SALE_DT,
1 as EmpWorkDayRank
Resident SalesData
Order By EmployeeID, Sales.SALE_DT desc;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Lauri,
That is a great idea. I had not considered that approach. I will give that a try and report back.
Thank you!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, Lauri! This will work as a solution.
