Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have an application where annual sales data is displayed in a simple straight table that ranks sales people. Now, my manager wants to see each of the sales figures for each week if they select it. So the annual data looks like this:
Rank | Employee | Total Sales |
---|---|---|
1 | John | 1,456,895 |
2 | Paul | 1,223,564 |
3 | George | 987,123 |
4 | Ringo | 402,564 |
I've edited the script to calculate the accumulative total by week and set that to display if a week/weeks are selected. The only problem is that if an employee doesn't register any sales for a week, they are omitted from the table, and the Rank becomes invalid/incorrect. Is there any way to include them in the table when there is no data? Also, there could be 4/5 week stretches where sales people might not register a sale.
Here is an example of what I'm seeing now. If I select week 24, all users have sales data:
Rank | Employee | Total Sales |
---|---|---|
1 | John | 600,234 |
2 | Paul | 534,276 |
3 | George | 307,659 |
4 | Ringo | 125,967 |
But if I select week 25, not all users have sales data and the table looks like this:
Rank | Employee | Total Sales |
---|---|---|
1 | John | 642,896 |
2 | Ringo | 142,215 |
But what I need to see is:
Rank | Employee | Total Sales |
---|---|---|
1 | John | 642,896 |
2 | Paul | 534,276 |
3 | George | 307,659 |
4 | Ringo | 142,215 |
Can this be done?
What are the expression that you have used here?
Hi Sunny,
I used to use:
Sum({<Month=, Date=>} Sales)
But after creating the weekly accumulation in the script, now I use this on the table:
if(GetSelectedCount(Week) = 1,
Only({<Month=, Week=>} Sales_WeekAccum),
Sum({<Month=, Date=>} Sales)
)
There is no transaction data for week 25 ?
So it seems that your script is missing Sales_WeekAccum for period where Sales i not available. May be fix that in the script or for any work around it might be helpful to have a sample to look at
Hi Sunny,
I'm unable to share any scripting/data on this one. How would it be possible to fix in the script? Inserting dummy rows if a value is missing?
Make sure accumulation takes place even when sales is 0 in a particular week. You might have to add all weeks for all your Employees before you perform the accumulation. I would try something like this:
Table:
LOAD Employee,
Week
FROM ....;
Join(Table)
LOAD Distinct Week
Resident Table;
and then move forward from there....
Thanks Sunny, I'll give that a go. Each sales week differs in the amount of weeks so I won't be able to hard code it. I'll trying populating the missing weeks on the fly for each year and let you know how I get on.
Yes, hard-coding won't be a good solution. I made a small mistake above, try this
Table:
LOAD Employee,
Week
FROM ....;
Join(Table)
LOAD Distinct Week as New_Week
Resident Table;
We want to create a cross join between Employee and all possible Weeks.
Hi Sunny, I think you are on the right track but this approach isn't feasible here. The above script is only adding each week number to each row.
45 - 50 weeks per year
100 + Employees
Between 0 & 40 sales per week (each sale is it's own row)
The result is too big and won't work. I'll let you know if I make any progress.