Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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
Hi,
Try this :
num((Column(1)-column(2))/column(2),'#0%')
Thanks & Regards,
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.
@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
Thank you for your kind help!
Thank you for your kind help!