Calculation on Result (summation of values)

[former_member188325]

Hi,

I need to perform some calculations on result of one CKF(reult is summation of all values).IS it possible?

regards

[Former Member · Accepted solution]

Hi Murali,

I reproduced the situation with getting price from the KF attribute of the char. It’s an attribute with a currency. I succesfully created a CKF from formula variable, multiplied NODIM(NOERR(CKF)) by NODIM(NOERR(qnty)). %GT gaved me the result.

What is a possible difference between my situation and yours (it may give a clue):

- my KF attribute was not marked as ‘Attribute only’

- formula variable in the from-to-difference was set to TO

- my attribute was time-independent

- my char with KF attribute was not compounded

So, GT% should clearly work. But something is wrong with getting value of KF attribute.

You may investigate it further if you want to.

Best regards,

Eugene

[former_member188325 · Accepted solution]

Hi Engune,

Thanku very much for u r time.only diff is that my char has two compound attributes.

problem may be there or in price values.we will take care of those things.I am closing this post.

thanks a lot again.

regards

[Former Member]

You can see this button at the bottom in the properties screen.

Now let's to experiment.

Put this new CKF into columns.

New formula. = NOERR(NODIM( (CKF)).

New formula. = NODIM(NOERR (CKF)).

New 2 formulas = %GT on the previous 2 formulas.

Let's see if we get rid of 0 or X.

Best regards,

Eugene

[former_member188325]

Sorry Engune,

i dint see any button for the same.where is it exactly?

regards

[former_member188325]

Engune,

i see Enhance button.

regards

[former_member188325]

Hi Engune,

this new CKF returns 'balank'.But if i take price into columns directly from formula variable it gives value.

[Former Member]

Hi Murali,

Along with files you sent to me everything looks reasonable. Though, I see minor discrepancies between a query definition and the result.

It’s obvious that the main culprit is a Price. I assume that you followed the “How to calculate with attributes”? If so, have you created the Price as calculated KF (left side of the designer screen, under ‘Key Figure’ node. Or you created it as a new formula in the columns structure? If the latter, try to create it as a CKF. Make sure that ‘After aggregation’ setting is set (Enhance button).

Also, I see that in the query definition ‘Qty Value (Stock Qty * Vpice)’ object has an icon without ‘f’ letter. So, it’s not a formula. Is it a CKF? How do you calculate this value? Check carefully, it might refer to the wrong objects.

Best regards,

Eugene

[former_member188325]

Hi Engune,

<i>Also, I see that in the query definition ‘Qty Value (Stock Qty * Vpice)’ object has an icon without ‘f’ letter. So, it’s not a formula. Is it a CKF? How do you calculate this value? Check carefully, it might refer to the wrong objects.</i>

Qty value is formula i.e. Qty*price.Since it has been hided we dont see 'f' letter on it.

thanks for u r time.will come back with other details.

regards

[former_member188325]

Hi Engune,

Now i am trying to create one more CKF as u said....Caluculated keyfigures-->rght clik->new CKF->rght click on formula variable(since price is an attribute of valuation)-->new formula var->select processing type as replace ment path-->selected char as valuation>replace variable with attribute value->select my attribute->next->finish...

now i can use this in my formula.but i dont see any enhance button as u said.

we are using 3.0B.

regards

[Former Member]

Material valuations like ‘#/#/0001108157A00’ means that valuation has 2 compounding attribute, ‘#’ means that the appropriate attribue is not assigned.

Questions:

1.What’s the type of Valuation (CHAR, NUMC?) And what is the length of its key?

2.Which attributes Valuation has. Which attributes are display and which navigation?

3.What are compounding attribute?

4.Price attribute – what type, with currencies, units?

5.If you have price in the query – how do yo determine it? Simply as attribute of Valuation? Or it is in the infoprovider?

6.Do you see prices and NOERR(price)?

7.How do you calculate total quantities? In the column? Or you have totals in the infoprovider?

8. Totals – is that total amount of particular material number through its valuations? Or through all materials?

I didn’t understan the structure of your query. It should look like this:

Material/valuation Price Quantity      Value
Mat1  SumOfMat1Prices SumOfMat1Q-ty SumOfMat1Value
   Valuation1         P1            Q1             V2
   Valuation2         P2            Q2             V2
   Valuation3         P3            Q3             V3
Mat2 SumOfMat2Prices  SumOfMat2Q-ty SumOfMat2Value
   Valuation1         P4            Q4             V4
   Valuation2         P5            Q5             V5
 …

9. Am I right about figures in Mat1 etc. rows?

10. What is the physical meaning of valuation?

11.Why do you multiply price by TOTAL quantity?

Take your time and please answer all questions.

Best regards,

Eugene

[former_member188325]

Hi Eugene,

i am sending screen shot of Query designer and Query result to u r yahoo id.

regards

[former_member188325]

Hi Engune,

here are the answers for u r Qs:

<i>1.What’s the type of Valuation (CHAR, NUMC?) And what is the length of its key?</i>

CHAR,18

<i>2.Which attributes Valuation has. Which attributes are display and which navigation?</i>

It has 21 attributes(both display and Nav).Price(valuation price) is display attribute.

<i>3.What are compounding attribute?</i>

Appreciation cycle(custome defined) and valuation type(0VAL_TYPE)

<i>4.Price attribute – what type, with currencies, units?</i>

its display attribue(0PRICE_VAL).its Amount,CURR,unit curreny is 0CURRENCY. aggregation and excep agg is summation

<i>5.If you have price in the query – how do yo determine it? Simply as attribute of Valuation? Or it is in the infoprovider?</i>

I have created a formula variable to make price available in calculations (i.e. to perform <u>Qty*Price</u>)

<i>6.Do you see prices and NOERR(price)?</i>

yes

<i>7.How do you calculate total quantities? In the column? Or you have totals in the infoprovider?</i>

Sum of two individual quantities.No i dont have total in infoprovider.

<i>8. Totals – is that total amount of particular material number through its valuations? Or through all materials?</i>

u can see in the doc which was sent to u

<i>10. What is the physical meaning of valuation?</i>

i dont know exactly.as per requirement its not required in the query.since i am using price formula variable,i have taken material valuation on the rows.

<i>11.Why do you multiply price by TOTAL quantity?</i>

it gives Qty value (Qty*Price).

I need to perform some calcutions on total Qty value.so i m taking SUMGT(Qty value).and it returns x.if i take SUMGT(Qty) it works fine and SUMGT(Price) returns X.

regards

[Former Member]

Material valuations like ‘#/#/0001108157A00’ means that valuation has 2 compounding attribute, ‘#’ means that the appropriate attribue is not assigned.

Questions:

1.What’s the type of Valuation (CHAR, NUMC?) And what is the length of its key?

2.Which attributes Valuation has. Which attributes are display and which navigation?

3.What are compounding attribute?

4.Price attribute – what type, with currencies, units?

5.If you have price in the query – how do yo determine it? Simply as attribute of Valuation? Or it is in the infoprovider?

6.Do you see prices and NOERR(price)?

7.How do you calculate total quantities? In the column? Or you have totals in the infoprovider?

8. Totals – is that total amount of particular material number through its valuations? Or through all materials?

I didn’t understan the structure of your query. It should look like this:

Material/valuation Price Quantity Value

Mat1 SumOfMat1Prices SumOfMat1Q-ty SumOfMat1Value

Valuation1 P1 Q1 V2

Valuation2 P2 Q2 V2

Valuation3 P3 Q3 V3

Mat2 SumOfMat2Prices SumOfMat2Q-ty SumOfMat2Value

Valuation1 P4 Q4 V4

Valuation2 P5 Q5 V5

…

9. Am I right about figures in Mat1 etc. rows?

10. What is the physical meaning of valuation?

11.Why do you multiply price by TOTAL quantity?

Take your time and please answer all questions.

Best regards,

Eugene

[Former Member]

Unhide hidden columns and tell us what you see there?

Valuation - what's its tech name?

[former_member188325]

i see quantities and sum of quantities in pc.

ex(8,256 PC

8,527 PC

1,435 PC

985 PC

8,576 PC

9,333 PC...etc) if i take NODIM then we dont see PC.

material valuation is characterstc and its custom defined.valuation price(price) is attribute of material valuation.

regards

[former_member188325]

Engune,

what i noticed is %GT(toatl Qty) or SUMGT(total QTY) gives correct result.But it returns X when i include Price . i.e. %GT or SUMGT of(Toatl Qty*Price).

regards

[Former Member]

It would be vey useful if you provide in details the foll. Info:

1. Query design: what do you have in rows and columns, any variables or filters, restrictions on chars, restrictions on selections in columns (including formulas).

2. What do you see in result (describe each column, including any prefixes or suffixes like designation of currencies or units, or ERROR).

Is it proper to sum up amounts with different currencies?

Best regards,

Eugene

[former_member188325]

Hi Engune

1.I have material and material valuation in rows. there are no filters,restrictions on any object.only one formula variable i.e. valuation price which is an attribute of material valuation(thats y material valuation is considered in rows).

in rows i have a KF structure with two quantities(basic KFS in PC..hidden),sum of qunatities i.e. total quantity(hidden),qty value (i.e. Qty*price) and NOERR price(Hidden),and %GT of qty value.

i ahve used NODIM for quanties and NODIM and NOERR for price as u said.

2.I see material number; material valuation(ex:#/#/0001108157A00, 780/#/0001108157A00, #/#/0001108157R00, 780/#/0001108157R00,...etc),i dont know y these '#' are appearing;

Qty value (without any dimensons since i used NODIM)and %GT of value(Formula)as X.

regards

[Former Member]

Do the following.

You have your KF (Value?).

Create a new formula as NODIM(KF).

Create another formula as %GT <previous formula>.

Create a condition with Bottom% and/or Top% on the last formula. You can hide fields you don't need.

[former_member188325]

No Engune,

it didnt work out.I have already had one condidtion to display value's values in descending order.

formula %GT of Value displays X.

regards

[Former Member]

Follow the sequence I mentioned.

%GT should apply to a formula with NODIM, not to Value.

[former_member188325]

Hi Engune,

i did as u said.But still i see X for %GT. and i noticed that if apply %GT or SUMGT for a basic KF,it gives result.But when i apply %GT or SUMGT to CKF/Formula it displays X.

regards

[Former Member]

I mean that you should have this:

1. CKF Value = NODIM( Price * Quantity )

2. CKF % = %GT Value

Then create conditions on last formula.

[former_member188325]

Hi,

still it displays X for 2.CKF % = %GT Value. I dont understand whats wrong with this.

If i take only Quantity in CKF,then for %GT value,it gives no error.

regards

Message was edited by: cmd

[Former Member]

Do you have exception aggregations for Quantity and/or Price (RSA1, infoobject maintenance)?

Have you set 'Calculate Individual rows As'... or 'Calculate result As...' Quantity and/or Price (BEx, chars properties)?

Also try to modify CKF Value as NODIM( NODIM (Price) * Quantity )

Message was edited by: Eugene Khusainov

[former_member188325]

Yes Engune,

Exeception aggregation for Quantity is 'last value' and for price is 'summation'.price is formula variable in Bex.

i have set calculate result as "summation" fro all CKFS.witht this also getting error.

<i>Also try to modify CKF Value as NODIM( NODIM (Price) * Quantity )</i>

i have already tried with this option.

regards

Message was edited by: cmd

Message was edited by: cmd

[former_member188325]

Hi Engune,

i appreciate u r patience.thanks a lot for spending u r time.

Message was edited by: cmd

[Former Member]

Is the price a given value coming from the source system? Or you calculate it somehow?

Try

NODIM(NOERR(PRICE))*NODIM(Quantity)

and

NOERR(NODIM(PRICE))*NODIM(Quantity).

Message was edited by: Eugene Khusainov

[former_member188325]

Yes Engune,

Price value is comin from R/3.

<i>NODIM(NOERR(PRICE))NODIM(Quantity) and NOERR(NODIM(PRICE))NODIM(Quantity).</i>

this time result is blank istead of X.

regards

[Former Member]

Are you sure that you have price at all?

Or maybe some prices are erroneous?

Try to create a formula NOERR(PRICE) and see if you have 0 (blank) prices.

[former_member188325]

Yes Eugene,i have prices in master data.NOERR(PRICE) and PRICE gives same values in report.For few materials prices are 0 so NOERR(PRICE) too.

<i>

maybe some prices are erroneous?</i>

i didnt get this eugene.

regards

[Former Member]

If NOERR(PRICE) returns 0 it means that either price is really 0 or price is erroneous.

Now, when you have a CKF as NOERR(PRICE)

create a new ckf as NODIM(prev CKF) * NODIM( Quantity ),

in another ckf use %GT prev. ckf

Then createconditions.

Best regards,

Eugene

[former_member188325]

Yes Engune.but it shold display value for atleast error free prices.rght?

<i>Now, when you have a CKF as NOERR(PRICE)

create a new ckf as NODIM(prev CKF) * NODIM( Quantity ),

in another ckf use %GT prev. ckf</i>

result is same(blank and X).

regards

[Former Member]

Hi,

If in the rows you show somewhat aggregated data, for example, by material group (not individual materials) and in the group at least one material with erroneous price contributes, NOERR for this whole group will return 0!

Try to build the query where you show all individual materials!

Best regards,

Eugene

[former_member188325]

Hi Engne,

nw i have taken only material,material evaluation(since price is attribute of mat evaluation) in rows and quantity,value(quantity*price),and %GT of VAlue.Still is displays X for %GT value.

regards

[Former Member]

Hi,

You should change the foll.:

not simply Price, but a new formula NOERR(Price);

change a formula Value to NODIM(prev. formula) * NODIM(quantity);

Best regards,

Eugene

[former_member188325]

Hi,

Still it returns X for %GT or SUMGT of (NODIM(prev. formula) * NODIM(quantity))

regards

[Former Member]

Put your price from rows into columns!

Do you see prices in the column NOERR(PRICE)?

[former_member188325]

Yes its aleardy in columns.and i could see values for NOERR(PRICE).(some values are zero EUR)and i have EZK currencies also.

regards

Message was edited by: cmd

[Former Member]

Hi,

Using condition(s) with Top% you can show records which cumulated amount >= top% of the total result (descending order).

Using condition(s) with Bottom% you can show records which cumulated amount >= Bottom % of the total result (ascending order).

Just remember that several rows in one condition will be joined with logical OR. Several conditions will be joined with logical AND.

Best regards,

Eugene

[former_member188325]

Hi I am trying like this

CKF=%CT 'Value'. and with other percentage functions also.But its displaying X in the result.

regards

[former_member188325]

hi,

No other way to get the result value? (i.e. summation of value column) ex:value column contains 100,30,20...results is 1003020 = 150.No i should be able to use 150 in other calculations.

regards

[former_member188325]

I am trying with SUMGT which gives overall result but result displays X.

please treat it as urgent

regards

[Former Member]

It may show * if your Value has currency or unit. Try to use NODIM() function.

Best regards,

Eugene

[Former Member]

Try to get rid of X and use conditions as I wrote.

[former_member188325]

Hi Eugene i am not able to get rid off X even with NODIM.

regards

[former_member188325]

Hi,

Any specific settings to do in properties of CKF?

[Former Member]

What's the type of your KF, how it is calculated?

Does it have currency or unit?

Message was edited by: Eugene Khusainov

[former_member188325]

Yes Engune.Its calculated one.I am tring to display result of the "value" column (here i have already used NODIM to display "value"."value" is Quantity*price)in a seperate column by using SUMGT"Value".So that i can use this result in other calculation.am i rght?

Message was edited by: cmd

[former_member188325]

Hi,Here is the example.

Value CKF

30,000,768,450.0

4,689,586,959.8

214,500,000.0

205,589,277.2

146,207,880.0

143,074,000.0

118,245,699.0

Result:35,517,972,265.9

here CKF clulmn should display 35,517,972,265.9.i.e. result of Value column.Hope i am clear now.

regards

Message was edited by: cmd

[Former Member]

Calculate your Value WITHOUT SUMGT! Use just a production of quantity and price.

[Former Member]

Have you done this?

[former_member188325]

Sorry Engune,i didnt get u.and Problem is with price.if include price in CKF and apply %GT then it displays X.for sum of quantities it works fine with %Gt or SUMGT it works fine.

regards

[Former Member]

Can you elaboarte pls..

Ashish..

[former_member188325]

Hi,

i have a CKF and result is summation of all values(by using calculate result as....summation ).Now i want to calulate some say 80% of that result,...etc

regards

[Former Member]

You have a few function Like Percentage share of result,

Percentage share of query result etc..

Basically these functions are under the percentage functions tab in the formula builder..

The difference is that they calculate the percentage for results at different levels..

Hope this helps

Ashish..

[former_member188325]

Hi,

To be more clear....

want to display x for <=80% of that result(total value),Y as >80 y <=95% and Z as >95% of the result.

regards

[Former Member]

Create a calculated Key figure using percentage share of result..Then based on this new CKF you can use a formula for your condition

eg :

x (CKF <=80%) + Y( 80%< CKF <=95%)+ Z*(CKF >95%)

This will always give you result X,Y or Z.

CKF = % share of result

[former_member188325]

Hi Ashish,

Thanks for the same.let me try out.here i need to display all x,y,and z values.I think u r logic gives one of them only.

regards

[Former Member]

You can always play around with the logic as per your requirement..I just wanted to give you an idea..

Hope it helps solve your problem..

Good luck

Ashish..

[former_member188325]

YES ASHISH.

i will get back to u with result.