Pivot table

[m4u]

Hi all,

i create a pivot table like in the attached image,

the item category and "month" are used as dimensions, and i have few expressions for the total of each year.

i need to add another column on the end, to show the "ytd" value of each year.

how can i do that without adding dummy rows to my table with the value 'ytd' in month name?

[#2]

yes you can

[m4u]

?

[#4]

add cyclic with month and ytd to the pivot

[Miguel_Angel_Baeyens]

Hello Shlomo,

One way to get this is creating a new field in your master calendar called YTD so

InYearToDate(TempDate, Date($(vMaxDate)), 0) AS YTDFlag

Where TempDate is the date field you are using to build the calendar and vMaxDate is the maximum date in the data model.

then you can add a new expression

Sum({< YTDFlag = {'-1'} >} SalesAmount)

Which will return the sum you are looking for. since you are using Month as dimension, and this is a new expression, you will get a new column within each month.

As this will get the chart with a lot of redundant information, I'd create one expression for each month and a new one for the YTD, then remove "Month" from dimension, say for month

Sum({< Year = {2010}, Month = {1} >} SalesAmount)

and for YTD

Sum({< YTDFlag = {'-1'} >} SalesAmount)

Using the YTDFlag field as created above. I know it's more arduous but I don't see any other solution as to have month and YTD sharing the same dimension.

Hope this helps

[m4u]

thanks for answering. shlomo