Dynamic Year to Date Calculation

[#1]

Hello All,

I'm hoping to get some help with a set expression that dynamically calculates a YTD sum based on selections.  Attached is the qvw.

I'm successful on calculating YTD based on a selection of "Year" and 'Month" using the following.

sum({1<CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

Because I'm using a "1" I'm unable now to select a state and get correct results.

If I take out the "1" I get cumulative sum of only the "Year" and "Month" (MTD) that is selected.

I'm sure there is a simple solution here.

Thanks in advance for any help.

[its_anandrjs]

Hi,

I believe your expression work fines that is

sum({1<CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

and why you want to remove the 1 from expression.If you select Year then month it will calculate the YTD calculation.

[#3]

Yes...that is true but when I select a "State" (list box) I would expect to see a YTD result for the state selected.  With the 1 it does not consider the selection of state.

[#4 · Accepted solution]

Why not:

sum({<Year=, Month=,CalDate= {'>=$(=YearStart(Max(CalDate)))<=$(=Max(CalDate))'}>}NetCount)

[#5]

Thanks Dariusz!  That work perfectly.