How to create a Pivot table with stacked calculated column

[#1]

Hi

I have a pivot table like this below:

                     201601    201602  201603  201604  201605  (Grand Total)     (LastMonth-FirstMonth)

APPLE            100         200       300       400          500            1500                400

ORANGE        10          20          30         40            50              150                  40

I Have dimension of product and yearmonth.

I have measure of sales.

How can I add the second calculation (LastMonth-FirstMonth)?  The difficulty is in this PivotTable, I dragged yearmonth on the top,then it is difficult for me to add another calculated column.

Thanks

[sunny_talwar · Accepted solution]

Something along these lines

Percent of Total in Pivot Table

[petter]

DATA:
LOAD * INLINE [
PRODUCT, PERIOD, #
APPLE, 201601, 100
APPLE, 201602, 200
APPLE, 201603, 300
APPLE, 201604, 400
APPLE, 201605, 500
APPLE, TOTAL
APPLE, LMFM
ORANGE,201601, 10
ORANGE,201602, 20
ORANGE,201603, 30
ORANGE,201604, 40
ORANGE,201605, 50
ORANGE, TOTAL
ORANGE, LMFM
]
;

Add the two dimensions PRODUCT and PERIOD

Add the measure:

Pick( Match( PERIOD , 'LMFM' , 'TOTAL' )+1 , 
Sum([#])
,Sum( {<PERIOD={$(MaxPer)}>} TOTAL <PRODUCT> [#] ) - Sum( {<PERIOD={$(MinPer)}>} TOTAL <PRODUCT> [#] )
,Sum( TOTAL <PRODUCT> [#] )
)

Add two variables:

MaxPer    as     =Max(PERIOD)

MinPer     as     =Min(PERIOD)

For the data table it is quite easy to automate the generation of the extra rows that needs to be present in the table to get placeholders for TOTAL and LMFM like this:

FOR i=1 TO FieldValueCount('PRODUCT')
     LOAD FieldValue('PRODUCT',i) AS PRODUCT, 'TOTAL' AS PERIOD AUTOGENERATE 1;     
     LOAD FieldValue('PRODUCT',i) AS PRODUCT, 'LMFM' AS PERIOD AUTOGENERATE 1;
NEXT

[MK_QSL]

Data:

CrossTable(MonthYear, Sales)

Load * Inline

[

  PRODUCT, 201601, 201602, 201603, 201604, 201605

  APPLE, 100, 200, 300, 400, 500

  ORANGE, 10, 20, 30, 40, 50

];

NoConcatenate

Final:

Load PRODUCT, Date#(MonthYear,'YYYYMM') as MonthYear, Sales Resident Data;

Drop Table Data;

Test:

Load * Inline

[

  Dim

  1

  2

  3

];

Now create a pivot table

Dimension

PRODUCT

=Pick(Dim,MonthYear,'GrandTotal','Diff')

Expression

=Pick(Dim,

  SUM(Sales),

  SUM(TOTAL <PRODUCT>Sales),

  SUM({<MonthYear = {"$(=Date(Max(MonthYear),'YYYYMM'))"}>}Sales)-SUM({<MonthYear = {"$(=Date(Min(MonthYear),'YYYYMM'))"}>}Sales)

  )

[sunny_talwar]

Manish I don't think you need TOTAL here:

=Pick(Dim,

  SUM(Sales),

  SUM(TOTAL <PRODUCT>Sales),

  SUM({<MonthYear = {"$(=Date(Max(MonthYear),'YYYYMM'))"}>}Sales)-SUM({<MonthYear = {"$(=Date(Min(MonthYear),'YYYYMM'))"}>}Sales)

  )

This should work also

=Pick(Dim,

  SUM(Sales),

SUM(Sales),

  SUM({<MonthYear = {"$(=Date(Max(MonthYear),'YYYYMM'))"}>}Sales)-SUM({<MonthYear = {"$(=Date(Min(MonthYear),'YYYYMM'))"}>}Sales)

  )

[MK_QSL]

Yeah.. through Cartesian Join..!

[sunny_talwar]

Yes kind of sort of