Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My Data:
Customer Name | Month | Year | CummPerc Cube | Commission |
A | 01 | 2012 | 0.75 | 1000 |
A | 02 | 2012 | 0.80 | 2000 |
A | 03 | 2012 | 0.85 | 3000 |
B | 01 | 2012 | 0.80 | 250 |
B | 02 | 2012 | 0.85 | 500 |
B | 03 | 2012 | 0.90 | 750 |
A | 01 | 2013 | 0.65 | 2000 |
A | 02 | 2013 | 0.75 | 4000 |
A | 03 | 2013 | 0.95 | 6000 |
B | 01 | 2013 | 0.80 | 1000 |
B | 02 | 2013 | 0.85 | 2000 |
B | 03 | 2013 | 0.90 | 3000 |
I need an expression where the user selects a Year and a Month and a percent (based on a Variable) and it returns the sum of all commission for that year and all months <= Month selected, IF the total commission IF "CummPerc Cube" is <= the variable selected.
So for example if the user selects Year: 2012, Month: 02 and the Variable of .82 the the returned object would be:
Customer Name | CummPerc Cube | Commission |
A | 0.80 | 3000 |
because Customer A is the only one that had "CummPerc Cube" value of under .82 in the month/year that was selected.
There's probably a better way but this seems to work:
Sum({1<Month={"<=$(=Max(Month))"},Year=P(),[CummPerc Cube]={"<=$(=vCommission)"},[Customer Name] = P({$<[CummPerc Cube]={"<=$(=vCommission)"}>})>} Commission)
See attached.
Hope this helps,
Jason
There's probably a better way but this seems to work:
Sum({1<Month={"<=$(=Max(Month))"},Year=P(),[CummPerc Cube]={"<=$(=vCommission)"},[Customer Name] = P({$<[CummPerc Cube]={"<=$(=vCommission)"}>})>} Commission)
See attached.
Hope this helps,
Jason
Jason -
Awesome! I think this will work great. Thanks
Steve
You may want to remove the '1' at the start of the set analysis to keep other selections you may have.
Jason -
Any ideas on why it seems to only sum on the month I select...not all months <= the month selected.
Steve
I think it worked OK for me. Can you post your application that isn't working?