topic Re: Pivot Table with Last Three Month and YTD in App Development

Pivot Table with Last Three Month and YTD

Nin_04 — Mon, 12 Feb 2024 19:33:32 GMT

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

Re: Pivot Table with Last Three Month and YTD

Anil_Babu_Samineni — Mon, 12 Feb 2024 19:39:28 GMT

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

Re: Pivot Table with Last Three Month and YTD

vinieme12 — Tue, 13 Feb 2024 00:55:00 GMT

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

)