Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
vish123
Creator III
Creator III

How to show growth rate in columns for years which are added as a columns in pivot table

Hi Team,

Suppose i have below data :

LOAD
[Row ID],
[Order ID],
[Order Date],
[Year],
[Ship Date],
[Ship Mode],
[Customer ID],
[Customer Name],
[Segment],
[Country],
[City],
[State],
[Postal Code],
[Region],
[Product ID],
[Category],
[Sub-Category],
[Product Name],
[Sales],
[Quantity],
[Discount],
[Profit]

FROM [lib://Desktop/Sample - Superstore.xls]
(biff, embedded labels, table is Orders$);

Once loading data into Qlik I want to show Year to year growth in pivot table like below

vish123_0-1644327813042.png

The above done in excel. What i want is , i need to calculate

1. growth from 2015 to 2016 

2. growth from 2016 to 2017

If the both are greater than 15% then i should display as "High Growth" otherwise "Low Growth".  I have done this in excel using below formula.

=IF(AND(((M5-L5)/L5)>0.15,((N5-M5)/M5)>0.15),"High Growth","Low Growth") but this cannot be straight forward in Qlik Sense.

I have created a pivot table as below in Qlik Sense. can someone please suggest how can i create a column as above mentioned in pivot table.

vish123_1-1644328174124.png

 

Labels (1)
1 Solution

Accepted Solutions
vish123
Creator III
Creator III
Author

Hi Johanos,

Thank you for your input but data is not in that format to do the caluculation on the columns in a pivot table.

I have resolved this issue by using Generic key word in load script.

 

View solution in original post

2 Replies
Sir_Johanos
Contributor II
Contributor II

if you want add 1 column:

You can try to use If(secondaryDimensionality()>0, sum(Plan), sum(aggr( ...etc )) )

it will change total formula to other

if you want add 2 column:

You can create dimantion from value of Year and total and comment [2014,2015,2016,total,div,comment]

and then try to make different formula for value wich are not Year.

But in this case you will have some not comfortable filters. maybe you can hide this dimantion _YearTotal.

 

vish123
Creator III
Creator III
Author

Hi Johanos,

Thank you for your input but data is not in that format to do the caluculation on the columns in a pivot table.

I have resolved this issue by using Generic key word in load script.