YTD in Graph

[#1]

Hello,

My Data is Build with a Account_Id, Year, MonthNbr, MonthValue

Now i would like in a Pivot Table see by Month its YTD (12 month vertical) and beside ther YTD values.

A code Like SUM(IF(??Month??<MonthNbr, MonthValue, 0) should do it, but how can i test the value of the month in the pivotgrid versus the month in the data, as it is based on the same field.

Kind Regards.

[amars]

Hi harry,

I am not sure, if I have got it right but instead of using a formula to show it this way , try using full accumulation.

Regards

Amar

[#3]

If there is more than one year of data available, you will need to have a year field to make sure the YTD data you grab is from the same year, but you can use set analysis to accomplish this:

sum({$<Year = {$(=only(Year))}, MonthNbr = {"<=$(=max(MonthNbr))"}>} MonthValue)

[#4]

I missed in the title of the topic, my graph is a Pivot Table, and i can't use full Accululation.

@Sue

I've tried this but i'm getting only the month values in the YTD, changing the comparison to just '=' results in the same as '<='

If you want i could post the qv here, but only if yoy have time to watch it. Do'nt want to force in lossing time.

[#5]

Due to time zones i will still follow this entry from @ home.

But i will already post the qv

You will see that in the sql-query i've also the ytd value as a figure, but its that that i would like to 'disable', in other queries this would be to big to keep it readable.

[#6]

I may be able to play with this later this evening (USA Mountain time).

[#7]

Thx, @Home (GMT+1, Belgium Time)