Calculating the % in the query...

[Former Member]

Hi,

There is a column in the query , which contains 0's and 1's.

I want to calculate the % of all 1's in the query as a result i.e

(counting all values <> 0/counting all values) * 100

Is is possible through formula??

Thanks.

[Former Member · Accepted solution]

Hi Jeetu,

Example gven by you,

1) I have a formula which contains 0's and 1's ( Result calculated as summation)

2) I created New formula which counts all rows in the report.

by putting =1 in the formula. I keep property of result as Summation.

and sometime I tested even with counting all values option. But didnt get the result

3) I want to see the Final result in the report.

Try the formula, this will work in your case

(Step 1/ SUMCT Step2) * 100 or Step1 %A SUMCT Step2 and change the settings of Calculate Result as to "Total" and if you want, hide the Single value. Because it doesn't make sense displaying at single value.

Hope this helps you.

Regards,

Venkat.S

[Former Member]

Hi Jeetu,

Please try col1 %A SUMCT (col1)

where col1 is ur column with 1's and 0s.

Regards,

Rathy

[Former Member]

Go to the display tab of this KF in Query designer and set the display of result as "Counter" because you have to count the no. of result rows, you dont have to SUM it. In that case, you will get the Denominator...i..e total No.(including 1 and 0).

If you are using DSO/masterdata, then you can make use on 1ROWCOUNT infoobject.Its available in the Package dimension and always give the no. of counts.

Thanks...

Shambhu

[Former Member]

Hi,

I tried that way also , but still its not wotking.

I put the problem again to make it more clear...

1) I have a formula which contains 0's and 1's ( Result calculated as summation)

2) I created New formula which counts all rows in the report.

by putting =1 in the formula. I keep property of result as Summation.

and sometime I tested even with counting all values option. But didnt get the result

3) I want to see the Final result in the report.

eg. if I have total 20 records and 1 's are 5. Then result

should display as 5/20*100 = 25 % ( In Overall result row).

But when I put the formula ( step1/step2*100 or step1%Astep2)

I get calculation done for all the rows.

But the final result row , Display 'X , which is the most important

value for report and is not calculated.

Is there anyway to calculate the Overall % ?

Let me know if I miss anything/step.

Thanks.

[former_member181964]

Hi, Jeetu

Just try with...

Goto Properties of the formula/keyfigure in columns and then check with the options Calculated Results as.. and Calculated Single value as, it will work, try this.

You may find different options in that.

Thanks

Reddy

[Former Member]

I tried with all the relevant option there ...

But doesnt get the right result.

Thanks.

[Former Member]

Yes this is possible....first count the no. all 1's .....once this is done, you can create a formula to calculate %. Though its much easier in backend.

Thanks..

Shambhu

[Former Member]

Hi,

I created the columns and used %A function , I get the result row as " X " for the overall result , which is most important for the final % .

I count all 1's as summation for all 1's.Any idea what I should do to remove X and display the correct result in the Overall result row.

Thanks.

[Former Member]

Hi Jeetu,

You can try out this solution:

Please create 3 formulas:

1) Formula with default value = 1, this wil;l get you the record count at the report level.

2) create a formula as (KF Name == 0) * 1; this will get you the count for all the keyfigure with value as 0, similarly create a nother Formaula for getting the count of KF for value 1.

3) using this Formula you can carry out the further calution in the report.

Hope this helps...

Regards,

Umesh...