topic Re: % Change Column along same measure in App Development

% Change Column along same measure

shaosiong — Fri, 05 May 2023 08:38:06 GMT

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

Re: % Change Column along same measure

Rohan — Fri, 05 May 2023 09:18:29 GMT

Hi,

Try this :

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

Thanks & Regards,

Rohan.

Re: % Change Column along same measure

Rohan — Fri, 05 May 2023 09:32:03 GMT

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.

Re: % Change Column along same measure

Kushal_Chawda — Fri, 05 May 2023 10:41:49 GMT

@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

Re: % Change Column along same measure

shaosiong — Mon, 08 May 2023 09:41:22 GMT

Thank you for your kind help!

Re: % Change Column along same measure

shaosiong — Mon, 08 May 2023 09:41:49 GMT

Thank you for your kind help!