% Change Column along same measure

[shaosiong]

Dear Qlik Heroes

Any ideas how I can get the above image into a pivot chart in qlikview?

I have the field columns and values for the dimensions Current Year L3M and Previous Year L3M across the measures 1 and 2 but I need to present the "% CHANGE" Column as well for the relative measures in the same row on the same chart.

Can anyone point me in the correct direction how to do this?

Appreciate your help!!

Regards

Shao 

[Rohan]

Hi,

Try this :

num((Column(1)-column(2))/column(2),'#0%')

Thanks & Regards,

Rohan.

[Rohan]

Hi,

Alternate way :

Add this inline in the end of your script;

Dummy :

Load * Inline[

Dim, Sort

CFY,1

LFY,2

Change,3];

In the front end pivot,

Add Dim & Month as Dimensions in the pivot table in Column,

& in Measures add 

Measure 1 : Pick(Sort,

num(CFY L3M expression,'#,##0'),

num(LFY L3M Expression,'#,##0'),

num((CFY L3M expression-LFY L3M expression)/(LFY L3M expression),'#0%'))) &

Measure 2 : Pick(Sort,

num(CFY L3M expression,'#,##0'),

num(LFY L3M Expression,'#,##0'),

num((CFY L3M expression-LFY L3M expression)/(LFY L3M expression),'#0%')))

& drag the Values to the Rows.

Let me know in case of any queries.

Thanks & Regards,

Rohan.

[Kushal_Chawda · Accepted solution]

@shaosiong  try below

Create pivot table with Row & Column Dimension. You can modify your values according to your need
Row Dimension:
=ValueList('Measure1','Measure2')

Column Dimension 1:
=ValueList('CY','PY','%DIFF')

Column Dimension 2:
Month

Mesure:
pick(match(ValueList('Measure1','Measure2'),'Measure1','Measure2'),

// Measure 1 CY,PY & %DIFF
Pick(match(ValueList('CY','PY','%DIFF'),'CY','PY','%DIFF'),
sum({<Year={"$(=max(Year))"}>}Sales),
sum({<Year={"$(=max(Year)-1)"}>}Sales),
num(sum({<Year={"$(=max(Year))"}>}Sales)/
sum({<Year={"$(=max(Year)-1)"}>}Sales)-1,'#0.00%')),

// Measure 2 CY,PY & %DIFF
Pick(match(ValueList('CY','PY','%DIFF'),'CY','PY','%DIFF'),
sum({<Year={"$(=max(Year))"}>}Quantity),
sum({<Year={"$(=max(Year)-1)"}>}Quantity),
num(sum({<Year={"$(=max(Year))"}>}Quantity)/
sum({<Year={"$(=max(Year)-1)"}>}Quantity)-1,'#0.00%')))

Enable "Show Total" for Dimension Month

Remove All Sorting options for Valuelist dimensions on Sorting Property

[shaosiong]

Thank you for your kind help!

[shaosiong]

Thank you for your kind help!