Hi Everyone, this one is a bit complex, and I'm hoping that it's possible.
Let's assume in my data world that people sell widgets.
I've created a graph that shows the count of people by the number of widgets sold using the AGGR function. basically: AGGR(COUNT(widget), person) ie: tell me how many people sold 6 widgets, how many sold 7, etc.
Now, in my world, if you sell 7 widgets at the end of a quarter, I'm going to give you $10. If you sell 8 widgets, I'm going to give you $12, and so on, and so on in an arbitrary schedule.
What I want to display is: If we paid out these rewards TODAY, how much money would I be on the hook for? So, I want to take the aggregated value, look up the reward value for that number of widgets, and then multiply by the number of people who sold that number of widgets, and display that value in a handy-dandy pivot table.
For my proof of concept, I've dummied in the schedule using an inline load, using the row index as the number of widgets, and the value in the column as the dollar value of the reward I will pay out. I want to PEEK() into this table, using the value from my AGGR function to determine the value, and multiply it by the count people... but I can't. I get nothing.
I'm going to keep poking, but some guidance would be much appreciated!
Well, I'm having a hard time coming up with a chart solution, and I really hate doing aggregations in the script. But here's a script solution since I'm giving up:
[Sales]: LOAD ceil(rand()*100) as Person ,1 as Widget AUTOGENERATE 1000 ; [Rewards]: LOAD recno() as Widgets ,recno()+ceil(rand()*10) as Reward AUTOGENERATE 100 ; INNER JOIN ([Rewards]) LOAD Person ,count(Widget) as Widgets RESIDENT [Sales] GROUP BY Person ;
