Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@Nin_04 I guess this is doable, Do you have sample QVF file to work with?
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
)