3 Replies Latest reply: Sep 16, 2013 3:38 AM by Gysbert Wassenaar

# YTD Value (Set Analysis)

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)

• ###### Re: YTD Value (Set Analysis)

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.

• ###### Re: YTD Value (Set Analysis)

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...