How to create following table

[narayanamsn]

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?

stalwar1‌

[trdandamudi]

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]

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 gwassenaar‌ hic‌  stalwar1‌

Unable to get comparison in the same table.

[trdandamudi]

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

[sunny_talwar]

This?

[narayanamsn]

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

[narayanamsn]

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

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]

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)