Moving the Month Dimension to the X- Axis

[tom906123]

Hello,

I am trying to build a straight table to look like the below but I am having issues moving the month - year column from the side to the top.

This is how the current presentation looks:

Customer	Cutomer Account #	Jan-18	Month - Year
abc	927	$ 7,182.00	Jan-18
abc	927	$ 4,456.00	Feb-18
xyz	830	$ 5,267.00	Jan-18
xyz	830	$ 743.00	Feb-18
abc	752	$ 5,374.00	Jan-18
abc	752	$ 4,211.00	Feb-18
xyz	164	$ 2,508.00	Jan-18
xyz	164	$ 6,177.00	Feb-18

This is an example of the desired outcome:

Customer	Cutomer Account #	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
abc	927	$ 7,182.00	$ 4,456.00	$ 5,493.00	$ 4,289.00	$ 3,979.00	$ 3,178.00
abc	752	$ 5,374.00	$ 4,211.00	$ 368.00	$ 7,809.00	$ 711.00	$ 6,681.00
xyz	830	$ 5,267.00	$ 743.00	$ 9,517.00	$ 8,747.00	$ 6,323.00	$ 2,340.00
xyz	164	$ 2,508.00	$ 6,177.00	$ 5,619.00	$ 1,227.00	$ 9,847.00	8,482.00

Any suggestions / tips would be greatly appreciated!

[sunny_talwar]

Why don't you use a pivot table instead which would allow you to pivot the month dimension to the top?

[chrismarlow]

Hi Tom,

I agree with Sunny generally, but have had cases where I've needed to mimic a more traditional report that contained fixed range of days that looked a bit like it should pivot, then had columns for month to date, budget, delta/variance etc that were a struggle to add to the pivot.

Generally that has involved either;

1) Having variables set in the script so varD1='Jan-18' (by some code that is clever enough to know that when you run this in the future and January is no longer valid then Feb will be the next date ...) then using set analysis with the variable so (Sum({<[Month - Year]={'$(varD1)'}>}amount) - note this has caused me all sorts of fun with date formats over the years

2) Where I needed to be able to flip a dashboard between sets of these dates (so last month end view, last quarter end view), having a linked calendar table to switch between the date sets (this also got me over my phobia of QlikView and dates, see point 1)

Cheers,

Chris.

[sunny_talwar]

but have had cases where I've needed to mimic a more traditional report that contained fixed range of days that looked a bit like it should pivot, then had columns for month to date, budget, delta/variance etc that were a struggle to add to the pivot.

You can do this in pivot table too... it's not straight forward, but is doable.... and I would do it in straight table rather than created 15-20 or more expressions.

[chrismarlow]

Sunny - agreed, for some of the example I am thinking of we even ended up using an expanded pivot table (the display of grouping/partial sums was closer to the original report format), but with no horizontal pivoting at all, everything based on expressions ...

[sunny_talwar]

You can get the idea from this post, but this can be expanded upon

Percent of Total in Pivot Table

[sunny_talwar]

Another example, but with additional headers in the row, instead of column

Percentage Subtotal‌

[Anil_Babu_Samineni]

How this value came - for March month as we don't have data in source?

abc

927

$ 7,182.00

$ 4,456.00

$ 5,493.00

$ 4,289.00

$ 3,979.00

$ 3,178.00