Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narayanamsn
Creator
Creator

How to create following table

Hi,

Have below scenario.

Below is the sample data.

          

CompanyMetricQtrValue
aSalesQ1'17590
aSalesQ2'17572
aSalesQ3'17889
aSalesQ4'17793
aSalesQ1'18784
aSalesQ2'18758
aSalesQ3'18746
aSalesQ4'18268
bSalesQ1'17561
bSalesQ2'17968
bSalesQ3'17627
bSalesQ4'17660
bSalesQ1'18999
bSalesQ2'18664
bSalesQ3'18324
bSalesQ4'18617
aExpensesQ1'17127
aExpensesQ2'1798
aExpensesQ3'17128
aExpensesQ4'17138
aExpensesQ1'1871
aExpensesQ2'18116
aExpensesQ3'1854
aExpensesQ4'18147
bExpensesQ1'1799
bExpensesQ2'17102
bExpensesQ3'1787
bExpensesQ4'1756
bExpensesQ1'18118
bExpensesQ2'18133
bExpensesQ3'18146
bExpensesQ4'1854

Trying to get the below comparison table

Table.PNG

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?

stalwar1

1 Solution

Accepted Solutions
sunny_talwar

Check attached

Capture.PNG

Now if we may, please close this thread by marking correct and helpful responses and keep any further additions for another thread. The reason I ask this is because we have already tried to work on multiple things on this thread and it might make it easy for future readers if we stop this here and create new thread with a better heading for add-on requirements.

Best,

Sunny

View solution in original post

13 Replies
trdandamudi
Master II
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:

GENERIC Load * Inline [
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
]
;

narayanamsn
Creator
Creator
Author

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.

trdandamudi
Master II
Master II

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

sunny_talwar

This?

Capture.PNG

narayanamsn
Creator
Creator
Author

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

sunny_talwar

Check now

Capture.PNG

narayanamsn
Creator
Creator
Author

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

sunny_talwar

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

Capture.PNG

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

narayanamsn
Creator
Creator
Author

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)