5 Replies Latest reply: Sep 12, 2013 5:52 AM by Jason Michaelides

# Choosing Max Month and YTD

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.

• ###### Re: Choosing Max Month and YTD

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

• ###### Re: Choosing Max Month and YTD

Jason -

Awesome! I think this will work great. Thanks

Steve

• ###### Re: Choosing Max Month and YTD

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

• ###### Re: Choosing Max Month and YTD

Jason -

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

Steve

• ###### Re: Choosing Max Month and YTD

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