3 Replies Latest reply on Feb 18, 2016 1:25 PM by Shinichiro Murakami

# Calculations to convert column to row

I simplify my dataset to the table as below. Here is my question, I need to create four Calc fields highlighted in yellow.

"Current-1 Status" calculation field should display the Status from previous Quarter Q4/2015, which is Green.

"Current-2 Status" calculation field should display the Status from Q3/2015, which is N/A.

"Current-3 Status" calculation field should display the Status from Q2/2015, which is Green.

"Current-4 Status" calculation field should display the Status from Q1/2015, which is Yellow.

I can't figure out the Calc to get what I want. Can someone help me with the Calc or guide me to the right direction?

Thanks so much!

• ###### 1. Re: Calculations to convert column to row

Jenny,  I don't know how you use this , but just reflected your simple request.

[Current 1]

if [Year]=2016 then

{fixed: min(

if {fixed:max([Year])} -1= [Year] and [Date]= "Q1" then  [Status] end

)} end

[Current 2]

if [Year]=2016 then

{fixed: min(

if {fixed:max([Year])} -1= [Year] and [Date]= "Q2" then  [Status] end

)} end

[Current 3]

if [Year]=2016 then

{fixed: min(

if {fixed:max([Year])} -1= [Year] and [Date]= "Q3" then  [Status] end

)} end

[Current 4]

if [Year]=2016 then

{fixed: min(

if {fixed:max([Year])} -1= [Year] and [Date]= "Q4" then  [Status] end

)} end

Thanks,

Shin

9.0 attached

• ###### 2. Re: Calculations to convert column to row

Thank you so much Shin! Your calc works great! I'm still going thru the transition from 8.3 to 9.1... Feel like I'm a dinosaur stilling using 8.X

The LOD is very cool and need get myself familiar with it!

Thanks again for your help! You saved my day!

• ###### 3. Re: Calculations to convert column to row

Jenny,

Sounds good.  Happy to hear that comment.

Yes, LOD is pretty useful tool.   That's the biggest game changer between 8.3 and even 9.2.

That's fun to learn this new formula for you, I promise.

Thanks,

Shin