sum by month and year

[#1]

Hi, I've got a straight table with the above data.

The YTD.Actual on the right most column only shows up in the last row.  This total is correct - the sum of dtAmount where dtYear =2013.

How do I make this field static so that it will always show that amount on each of the month?  If I select any month, that total becomes wrong - it shows the same value as dtAmount.

[tresesco]

Write your expression with set analysis like:

=Sum({<MonthField>} dtAmount)

[avinashelite]

Hi,

In your expression try like this:

sum({1<month>}YTD.Actual)

This will make the sum for the all the values. If u face any issue please share your qvw file

[#4]

hi, I've tried both your suggestions and it doesn't quite work.  I've attached a sample of my document.

I want the total for the whole year - ie. sum all dtAmount in year 2013.

This value should not recalculate based on my selection.

[#5]

Hi,

When u want a static value to be displayed on all the months. Why don't u use the year instead of month.

Suppose u want to calculate the total for all the months of 2013 year

U can frame a expression like this

Sum({$<year={2013}>}YTD.Actual)

[tresesco]

=Sum( {<dtPeriod>} total <PROJECT_ID,dtActivityNo> dtAmount)

Note: This is to ignore selection in the dtPeriod field only. If you want more fields in the exclusion list you have add them like  <dtPeriod, ...>.

Pfa

[#7 · Accepted solution]

For a year to date solution you want to include in your set analysis that period is less than or equal to the rows period as well, otherwise you'll end up with the years total on each line, rather than cumulative year to date

[#8]

Hi, thanks Marcus for the extra tip - that the total needs to be a running total within that year.  And thanks to everyone too.  I've tried your suggested but it's not fully working:

Sum({$<Year={Year}, Period={"<=Period"}>}

    Total<dtProjectID, dtActivityNo, dtYear>  dtAmount)

eg. activity no. 1325 have these amounts:

dtAmount     YTDAmount

7414.23     7414.23

9951.85     17,366.08

4173.15     21,539.23

The YTD keeps recalculating.  But I want the YTD values to be static - that no matter what month(s) I pick, they show those YTD amounts above.