How to use '/ '

William Yong

Forums

Hi,
I'm currently working on an automation to calculate ratios in IDEA. How am I going to perform "divide" in certain row in IDEA with another row.
Here's the scenario:
      Item (Type: Char)                 Amount (Numeric)
1.   Margin A                                           20
2.  Margin B                                           40
3.  Gross Profit                                    100
Gross Margin = Gross Profit / Margin A, 100 / 20 = 5%
How am I able to translate the above equation in IDEA for my analysis.
Any help would be greatly appreciated here.
Cheers.
William

Brian Element Mon, 03/30/2015 - 07:25

Hi William,

That is an interesting problem you have there.  So what other information is there in the database.  So is there other items that you want to find in other fields.  I guess what I am asking is does your database look more like that:

Item Amount1 Amount2 Amount2
Margin A 20 30 40
Margin B 40 60 80
Gross Profit 100 100 100

Something like this, the headers are the first field.  Different options, you could probably use a create a script that puts the information in the Item row as columns and changes the data, then it would be a simple exercise to create a virtual field.  So the end product would look like this:

Margin A Margin B Gross Profit
20 40 100
30 60 100
40 80 100

Another option would be to use the @GetNextValue option, that would problably work.  Can you give me a bit more info on what the entire database looks like and I will see if I can think of an option.

Thanks

Brian

William Yong Tue, 03/31/2015 - 00:34

Hi Brian,
Yes, that's what I thought so, to shift the fields under ITEM field to column field for calculation. To aid your understanding, I'm attaching the database and result screen that I wish to have.

 
2014
2013

Item
 
 

Margin 1
20
30

Margin 2
40
30

Margin 3
50
55

Margin 4
30
40

Margin 5
70
50

Gross Profit
210
205

 
 
 

Working1 (b8/b6%)
140
91

Working2 (b4+b5+b6) - b7 / b9)
96
95

 
 
 

 
 
 

Final Output Screen
 
 

Profitability
Ratios

2014
2013

Working1
140
91

Working2
96
95

My task is require to generate Working 1 & 2 in IDEA by referring to the formulae given. I believe I need to create a Virtual Field for Working 1 & Working 2 and input those formulae in the criteria/equation editor.
In the final screen, I would be able to see Working1 Ratio as 140% (for 2014) and 91% (for 2013). Does it sound complicated here?
Kindly assist with some sample script for this type of analysis here.
Thank you.
William 
 
 
 
 
 

Brian Element Tue, 03/31/2015 - 07:51

Hi William, I think you need a script where you can take the information that is in the columns and change it so that it is rows, once it is in rows then it wouuld be a simple matter to use a virtual field and the equation editor to recalculate.  I will see if I can come up with a script to do the "flip" for you.

Brian Element Mon, 04/06/2015 - 10:23

Hi William, you want to try out this script.  It will take columns and turn them into rows.  One of the columns must have the header information and you can only select the same type of columns, i.e. you can't mix numeric with character columns.  Once you use this all the information should be on the same row so then you can use the equation editor to perform your recalculation.  You can download the script here.