Math Calculation in Bex query

[Former Member]

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

[ccc_ccc · Accepted solution]

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

[Former Member · Accepted solution]

Hi Nanda,

Thanks for your reply. Like CO2, H2O i have many values SO,H2S,PM etc.. these are values of ZC_PARA Infoobject. So i dont think i can use structure. could you pls give me other solution.

Regards

Vijay

[former_member566306 · Accepted solution]

Hi Vasu,

Can you try doing this below.

In the calculations tab of formulas for max value use " Calculate result as Maximum" similarly for minimum value use " Calculate result as Minimum" .For average use "Calculate result as average"

I guess this will help.

Thanks,

Dharma

[Former Member · Accepted solution]

Thanks Dharma, Now Am getting the desired result. But still avg is not calculating. pleas refer below screen shot as example

For the daily avg column, result row showing correct value 37 as expected but individual records not showing.

Thanks

Vijay

[Former Member · Accepted solution]

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]

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