4 Replies Latest reply: Feb 16, 2012 11:36 AM by david.morris

# Do year to date total in a pivot table chart

Hello,

Please can you help me with this. I have a pivot table, CONDITION down the left axis and DISMONTH along the top axis. These are my dimension.

The expression and the values in the pivot table are at present a count of a unique identifier, CRN. So I have count of CRN by the CONDITION and by the DISMONTH in my pivot. What I want to do though is make this count a year to date count of the CRN. Hence displayed for DISMONTH Apr-2011 would be May-2010 to Apr-2011's count. For DISMONTH May-2011 you would have the year to date count Jun-2010 to May-2011.

I've tried a few things but ultimately to no avail. Any help, pointers greatly appreciated. Best wishes.

Dave.

• ###### Re: Do year to date total in a pivot table chart

Hi david,

Regards

Ashok

• ###### Do year to date total in a pivot table chart

Thanks a lot for the help Ashok. Much appreciated.I have learned from your qvw file, but I have discovered another approach that seems to work for me.

Thanks,

Dave

• ###### Do year to date total in a pivot table chart

Thanks for the reply Jagan. I've taken your expression and learned from it. I used the set analysis in a different way and it seems to work. Your help is much appreciated.

Regards,

Dave.

• ###### Do year to date total in a pivot table chart

Hi,

Try this expression

=Count({<DateFieldName={'>=\$(=MonthStart(Max(DateFieldName),  -11))<=\$(=Max(DateFieldName))'}>}CRN)

Hope this helps you.

Regards,

Jagan.