New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator

How to create following table

Hi,

Have below scenario.

Below is the sample data.

 Company Metric Qtr Value a Sales Q1'17 590 a Sales Q2'17 572 a Sales Q3'17 889 a Sales Q4'17 793 a Sales Q1'18 784 a Sales Q2'18 758 a Sales Q3'18 746 a Sales Q4'18 268 b Sales Q1'17 561 b Sales Q2'17 968 b Sales Q3'17 627 b Sales Q4'17 660 b Sales Q1'18 999 b Sales Q2'18 664 b Sales Q3'18 324 b Sales Q4'18 617 a Expenses Q1'17 127 a Expenses Q2'17 98 a Expenses Q3'17 128 a Expenses Q4'17 138 a Expenses Q1'18 71 a Expenses Q2'18 116 a Expenses Q3'18 54 a Expenses Q4'18 147 b Expenses Q1'17 99 b Expenses Q2'17 102 b Expenses Q3'17 87 b Expenses Q4'17 56 b Expenses Q1'18 118 b Expenses Q2'18 133 b Expenses Q3'18 146 b Expenses Q4'18 54

Trying to get the below comparison table

Can I fit all these in a single Table? I presume NO, in that case do I need to plot 3 different set of Values

(1. Absolute Values 2. Quarterly difference: Ex: Q1'18-Q1'17... 3. Quarterly difference proposition : Ex: Q'18/Q1'17 -1 )

I am unable to get the 2nd and 3rd set of Values  (i.e Diff, propositions).

Can anyone help me?

1 Solution

Accepted Solutions
Highlighted
MVP

Check attached

Best,

Sunny

13 Replies
Highlighted
Master II

Not sure 100% what you are looking for, but the below code will flip the Row 'Qtr' to multiple columns and then you can create the chart. Hope this helps:

Company, Metric, Qtr, Value
a, Sales, Q1'17, 590
a, Sales, Q2'17, 572
a, Sales, Q3'17, 889
a, Sales, Q4'17, 793
a, Sales, Q1'18, 784
a, Sales, Q2'18, 758
a, Sales, Q3'18, 746
a, Sales, Q4'18, 268
b, Sales, Q1'17, 561
b, Sales, Q2'17, 968
b, Sales, Q3'17, 627
b, Sales, Q4'17, 660
b, Sales, Q1'18, 999
b, Sales, Q2'18, 664
b, Sales, Q3'18, 324
b, Sales, Q4'18, 617
a, Expenses, Q1'17, 127
a, Expenses, Q2'17, 98
a, Expenses, Q3'17, 128
a, Expenses, Q4'17, 138
a, Expenses, Q1'18, 71
a, Expenses, Q2'18, 116
a, Expenses, Q3'18, 54
a, Expenses, Q4'18, 147
b, Expenses, Q1'17, 99
b, Expenses, Q2'17, 102
b, Expenses, Q3'17, 87
b, Expenses, Q4'17, 56
b, Expenses, Q1'18, 118
b, Expenses, Q2'18, 133
b, Expenses, Q3'18, 146
b, Expenses, Q4'18, 54
]
;

Highlighted
Creator

Hi Thirumala,

I had created Pivot table and interchanged expressions and dimensions to get the first table(absolute values per quarter)... but unable to get the quarterly difference (Q1'18-Q1'17)..

any suggestions gwassenaarhic‌  stalwar1

Unable to get comparison in the same table.

Highlighted
Master II

Take a look at the attached were I did the difference columns and hope this helps.

Highlighted
MVP

This?

Highlighted
Creator

absolutely this is the view.. Is there any way that to sort the Qtrs in sequence.. Ex: Q1'17, Q2'17...

Now the sequence is Q1'17, Q1'18 in your solution.

Kudos Sunny

Highlighted
MVP

Check now

Highlighted
Creator

cool. Have a query with the sort exp for dim " RangeSum(Only({1} Dim), QtrSort/1e10)".. what does this do?

for Diff and % change , still the sequence is altered. Can we use any conditional formatting for the Diff, % change value (Ex: bold for negative values)

Note : You had mastered the QV functions' applications. I was thinking to develop 3 charts, one for Values, 2nd one for Diif and last one for % change... But you had accommodated all in single table using Pick function and dim data island for 3 blocks

Kudos,

Sunny

Highlighted
MVP
 for Diff and % change , still the sequence is altered. Can we use any conditional formatting for the Diff, % change value (Ex: bold for negative values)

That was my mistake of removing the sort on text

 RangeSum(Only({1} Dim), QtrSort/1e10)".. what does this do?

Best way to see it would be to add it as an expression and see what you get... whatever you get... I am sorting that in the ascending order

Highlighted
Creator

Sequence is fixed with the selection of Text sort.. Any light on conditional formatting for the diff, % change blocks (bold or Text color for negative values)