Former Member

### Math Calculation in Bex query

Dear Experts,

I have the below data in BW.

Date Time(Hr)

CO2

(zk_value)

H2O

(zk_value)

20/2/2014 1 0.665939 6.141796 20/2/2014 2 0.584507 3.286 20/2/2014 3 0.602169 3.636601 20/2/2014 4 0.666684 3.030161 20/2/2014 5 0.694991 3.515662 20/2/2014 6 0.715507 3.424114 20/2/2014 7 0.740089 3.739442 20/2/2014 8 0.646752 2.978481 20/2/2014 9 0.549714 2.152406 20/2/2014 10 0.49801 2.804975 20/2/2014 11 0.431097 8.513797 20/2/2014 12 0.411588 50.08882 20/2/2014 13 0.237801 110.5091 20/2/2014 14 0.218378 96.62129 20/2/2014 15 0.242381 46.51961 20/2/2014 16 0.240304 19.21492 20/2/2014 17 0.326298 4.918987 20/2/2014 18 0.389933 4.256416 20/2/2014 19 0.404148 3.939687 20/2/2014 20 0.410364 3.430606 20/2/2014 21 0.416653 3.10039 20/2/2014 22 0.44833 3.06806 20/2/2014 23 0.481301 2.99557 20/2/2014 24 0.557959 12.70483

Now i need to represent the data in BI report as below.

Prarmeter          Max(in a day)     Min(in a day)   Daily Avg

CO_mgm          0.74                    0.21               0.48

H2O                  110.5                  2.15             16.85

Used Parameter and Date  as Characteristic info object (rows)

created 3 formulas in columns. In general tab of formula i selected (zk_value * 1) and in aggregation tab i  selected as max , min, avg in exception aggregation and used date as ref characteristic for all the 3 formula respectively but im not getting correct values. Im simply getting summation as the result.

Please guide me to achieve the result.

Thankyou

Vijay

10|10000 characters needed characters exceeded

Jan 05, 2015 at 03:50 PM

Hi Vijay,

1 Create two structures.

2 In Rows, right click on newly created structure->select new selection

3 Create two selections CO2 and H2O

4 Click on cell restrict first selection with CO2 and second H2O

5 Do same what you did earlier 3 formulas select min for both calculated single and result value

check and let me know.

Thank you,

Nanda

10|10000 characters needed characters exceeded
• Former Member Former Member

Hi vijay

you can use SUMGT data function in order to get on individual records on Daily Average.

Hope this helps.

Thank you

sunil

• Former Member
Jan 07, 2015 at 12:22 AM

Hi,

Create a couple of Calculated KF.

For each one add the original KF and add an "Aggregation" using MIN, MAX and AVG with a "Reference Characteristic" your DATE infoObject.

Add over your KF structure the ZC_PARA and the calculation will be broken for each value.

When you display the detail by DATE, each day shows your calculations.

Regards!

RG