Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Nin_04
Contributor II
Contributor II

Pivot Table with Last Three Month and YTD

Hello everyone,

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

Nin_04_0-1707752152113.png

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

Labels (1)
2 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vinieme12
Champion III
Champion III

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

)

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.