Choosing Max Month and YTD

[zagzebski]

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.

[Jason_Michaelides · Accepted solution]

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

[zagzebski]

Jason -

Awesome! I think this will work great. Thanks

Steve

[Jason_Michaelides]

You may want to remove the '1' at the start of the set analysis to keep other selections you may have.

[zagzebski]

Jason -

Any ideas on why it seems to only sum on the month I select...not all months <= the month selected.

Steve

[Jason_Michaelides]

I think it worked OK for me.  Can you post your application that isn't working?