topic Re: How to create a Pivot table with stacked calculated column in QlikView

How to create a Pivot table with stacked calculated column

Tue, 24 Jan 2017 15:52:57 GMT

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

Re: How to create a Pivot table with stacked calculated column

sunny_talwar — Tue, 24 Jan 2017 16:00:36 GMT

Something along these lines

Percent of Total in Pivot Table

Re: How to create a Pivot table with stacked calculated column

petter — Tue, 24 Jan 2017 16:29:02 GMT

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

Re: How to create a Pivot table with stacked calculated column

MK_QSL — Tue, 24 Jan 2017 18:44:10 GMT

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

];

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)

)

Re: How to create a Pivot table with stacked calculated column

sunny_talwar — Tue, 24 Jan 2017 18:49:12 GMT

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)

)

Re: How to create a Pivot table with stacked calculated column

MK_QSL — Wed, 25 Jan 2017 07:13:54 GMT

Yeah.. through Cartesian Join..!

Re: How to create a Pivot table with stacked calculated column

sunny_talwar — Wed, 25 Jan 2017 09:42:18 GMT

Yes kind of sort of