YTD Value (Set Analysis)

[zagzebski]

I am trying to get a YTD Commission. My data is below.

CustID	Cust	Month	Date	DateNum	DateYear	Prod	Commission	Commission-YTD
V|WALLF-1|B202	A	Feb	2/1/2012	40940	2012	B202	8142.24
V|MADAR-1|B202	B	Feb	2/1/2012	40940	2012	B202	1425.19
V|WISAL-1|B202	C	Feb	2/1/2012	40940	2012	B202	1923.95
V|NATIG-1|B202	D	Feb	2/1/2012	40940	2012	B202	4186.72
V|KICKV-2|B202	E	Feb	2/1/2012	40940	2012	B202	3917.07
V|PRODL-1|B202	F	Feb	2/1/2012	40940	2012	B202	918.97
V|GARDM-1|B202	G	Feb	2/1/2012	40940	2012	B202	1799.34

What I want (in the Commission-YTD column) is sum of all months up to the month month selected for that year. (If user selects Feb and 2012, then Commission-YTD column is a total of Jan 2012 and Feb 2012 for that particular row.

My current expression is bringing back 0's:

  Sum ({<[DateNum] = {$(=<Max([DateNum]))}>}Commission)

[Gysbert_Wassenaar]

Try Sum ({<[Month]=, [DateNum] = {'<=$(=Max([DateNum]))'}>}Commission)

Note, don't use Month, Date or DateNum as dimension in your chart. Or do it just to see what then happens.

[zagzebski]

Thanks! So this worked great but if I want this object to work for every month regardless of any selections how could I write the exdpression.

For example if the row has Feb 2012 data then it shows Feb 2012 YTD Commissions, If row is showing June 2013 data, then it shows June 2013 YTD Commissions, If the row is showing Nov 2011 data then it shows Nov 2011 YTD Commissions, and so on...

[Gysbert_Wassenaar]

In that case you can't use set analysis. The set is calculated once for the entire chart, not per row. See this document for an explanation and a solution: Calculating rolling n-period totals, averages or other aggregations