Evaluating one month, summing on YTD

[zagzebski]

I have the following type data:

^DeptCust	Dept	Customer	CustomerPct	Year	Month	Commission
A|1	A	1	40%	2012	Jan	100
A|4	A	4	50%	2012	Jan	150
A|3	A	3	55%	2012	Jan	200
A|2	A	2	60%	2012	Jan	250
A|5	A	5	100%	2012	Jan	300
A|1	A	1	40%	2012	Feb	400
A|2	A	2	60%	2012	Feb	450
A|2	A	2	60%	2012	Feb	500
A|3	A	3	65%	2012	Feb	550
A|4	A	4	100%	2012	Feb	600

I need an object where a year and month are selected and  a variable for the CustomerPct. So (use data above) if a user selects Feb and 2012 and a CustomerPct of 65%, then the object returns all data from February that has a % of 65% or less. This expression works fine:

sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)

and  would return all blue lines from Feb 2012.

However I need it to take all months = or less than the month selected FOR the combinations that are valid for February (A|1,A|2, and A|3).

The result would look like:

Dept          Commission     Distinct Customer Count

A               2450                              3

Looking for Commission to be YTD and how to get a distinct customer count.

[CELAMBARASAN]

Have Dept has dimesnion

Two expression:

1). Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)

2). Count({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >} DISTINCT Customer)

[zagzebski]

Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)

This formula doesn't work because if I select February it gives me ONLY February commission. I need February plus all previous months (ie YTD). So choosing Feb 2012 and the variable percent establishes what customer/dept combinations you want but then I need it to be all YTD commissions of that combination...essentially all the lines in blue in my data set and the object shows the result (above).

Thanks for helping!

[CELAMBARASAN]

Do you have date field?

Make use of it.

Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"}, DateField={">=$(=YearStart(Max(DateField)))<=$(=Max(DateField))"}, Year=, Month= >}Commission)