How to use '/ '
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
Hi Brian,
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
Hi William, I think you need
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.
Hi William, you want to try
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.
Hi William,
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:
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:
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