Auto Calendar - Past 7 Days

[DataHound]

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! 

[Lauri]

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?

[DataHound]

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. 

[Lauri · Accepted solution]

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;

[DataHound]

Lauri, 

That is a great idea. I had not considered that approach. I will give that a try and report back. 

Thank you! 

[DataHound]

Thank you, Lauri! This will work as a solution.