2 Replies Latest reply on Sep 27, 2012 6:19 AM by Taha Gökçe

"move to columns" and further calculations

Hi,

I have a data in my database in the form below:

 DATE GROUP TITLE TYPE 01.01.2012 A MAST 73A 02.01.2012 A MAST 73A 03.01.2012 A MAST 73B 01.01.2012 A ACT 73B 02.01.2012 A ACT 73B 03.01.2012 A ACT 73A

I want to see the total percentage of change of the type from "MAST" to "ACT" for corresponding (pair) rows. To do this; I think I have to take my data into Access and shape the data into the form below: (just "move to columns"). Then I can create a calculated field and find the percentage.

 DATE GROUP MAST ACT 01.01.2012 A 73A 73A 02.01.2012 A 73A 73B 03.01.2012 A 73B 73B

I am asking now if there is a way in Tableau to acquire this result without using Access?

I can visualize the second table above in Tableau, but I can't create a calculated field over that table because the data is originally in the first form, and how can it find the corresponding row in the first form to compare the equality?

• 1. Re: "move to columns" and further calculations

Hi Taha,

This can be done by using a table calculation, using a formula similar to the following:

(ZN(SUM([Number of Records])) - LOOKUP(ZN(SUM([Number of Records])), -1)) / ABS(LOOKUP(ZN(SUM([Number of Records])), -1))

Then, place this on the view. It will be necessary to edit how the table calculation is being computed--it will probably need an advanced compute.

Hope this helps a bit!

-Tracy

• 2. Re: "move to columns" and further calculations

Hi Tracy,
I think lookup function looks up +/-n rows according to offset point. But I don't have a regular table to use a determined "n". The pair row is somewhere there in the data.
I think it may only look up the pair row by using a "for loop", but is there any available "for loop"? If there is, will it be efficient to use here such a code like a macro?