Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Not applicable

Pivot - Cross Table - Percentage of specific columns

Hello everyone,

Need help. I have the following pivot table presented in a CrossTable format:

CodeYear201020112012
A00100120150
A01200180200
Total300300350

I want to create a calculated filed (expression) which calculates difference between 2012 / 2010 in percentage. The pivot table should look like this:

CodeYear201020112012%Change 2012 / 2010
A0010012015050%
A012001802000%
Total30030035016%

How can I do this?

Thank you.

6 Replies
Highlighted
robert99
Valued Contributor III

Re: Pivot - Cross Table - Percentage of specific columns

I don't think you can do it in this way

One option would be to use more than one expression (in either a pivot or straight table) using set analysis (such as)

dimension   = Code

and

expression 1  (2010)    Count ({<Year = {2010}>} ???? )

expression 2 (2011)    Count ({<Year = {2011}>} ???? )

expression 3  (2012)   Count ({<Year = {2012}>} ???? )

expression 4  (%) with number formatted as % 

(Count ({<Year = {2012}>} ???? ) -  Count ({<Year = {2010}>} ???? ))      / Count ({<Year = {2010}>} ???? )

Highlighted
Not applicable

Re: Pivot - Cross Table - Percentage of specific columns

Thanks RJ. I think this means a mess if number of years increase, and if I want to carry out variances between specific years. Any other suggestion?

Highlighted
Not applicable

Re: Pivot - Cross Table - Percentage of specific columns

Hi,

I feel, we can' do this through Pivot table. But, we can do the same from straight table. See the attachment.

Highlighted
Not applicable

Re: Pivot - Cross Table - Percentage of specific columns

Hi,


In general, we do compare data between the current year Vs last year (Or) Current Year Vs Past years.

If this is the case, then use the expression like below:

Sum({<Year = {'$(=Max(Year))'}>}Sales)

Sum({<Year = {'$(=Max(Year)-1)'}>}Sales)

Sum({<Year = {'$(=Max(Year)-2)'}>}Sales)

Expression for %Change is Same as mentioned in my last attachment.

Highlighted
Not applicable

Re: Pivot - Cross Table - Percentage of specific columns

Thanks Venu / RJ.

I think QV needs to come up with some easy way to accomplish this in a crosstab pivot table. Variances between years, months, is a normal management reporting activity seen from various crosstab dimensions.

Highlighted
robert99
Valued Contributor III

Re: Pivot - Cross Table - Percentage of specific columns

Another option is to use variables (setting variable overview etc)

You can for example have variables in set analysis

sum( {$<Year = {$(#vYear1)}>} Sales )

sum( {$<Year = {$(#vYear2)}>} Sales )

sum( {$<Year = {$(#vYear3)}>} Sales )


and use an input box to change the variables (Years) as required. (although I have not done this) You can set up the required variables from the input box