Pivot Table with Last Three Month and YTD

[Nin_04]

Hello everyone,

I need to develop a pivot table with the following structure:

all very easy to create if the requirement had been "only the last 3 month in the columuns" without YTD Column.  how I could add the YTD column? how can manage the YTD and last 3 months without impacting perfromace?If not the pivot table,  what other chart could I use?

Thanks in advance for the help

[Anil_Babu_Samineni]

@Nin_04 I guess this is doable, Do you have sample QVF file to work with?

[vinieme12]

Create an inline dimension table for column groups as below

Load * inline [
Period
1
2
3
4
]

In Pivot Table Use 

Row Dimensions: Product

column Dimension

= Pick(Period

, Maxstring({<Date={"$(=Date(AddMonths(Max(Datefield),-2)))"}>}MonthYearField)

, Maxstring({<Date={"$(=Date(AddMonths(Max(Datefield),-1)))"}>}MonthYearField)

, Maxstring({<Date={"$(=Date(Max(Datefield)))"}>}MonthYearField) 

,'YTD ' & Maxstring({<Date={"=$(=Date(Max(Datefield)))"}>} YearField)  )

3 Measures as below, replicate for each measure typeA,TypeB,TypeA+B :

=Pick(Period ,

, sum({<Date={"$(=Date(AddMonths(Max(Datefield),-2)))"}>} TypeA) //prev to prev month

, sum({<Date={"$(=Date(AddMonths(Max(Datefield),-1)))"}>} TypeA) //Prev MOnth

, sum({<Date={"$(=Date(Max(Datefield)))"}>} TypeA)  // Current Month

, sum({<Date={">=$(=YearStart(Max(Datefield)))<=$(=Date(Max(Datefield)))"}>} TypeA)  //YTD

)