Restricted key figure advice please...

[Former Member]

greetings sdn. i need your advise on how to go about this type of report layout.

i have 2 main columns, customer type and customer type b. and then 3 sub columns; consumption ranges and total.

each customer should only fall in one category, either type a or type b.

type a type b

(1-10) (11-20) tot (1-10) (11-20) tot

cust x 1 1

cust y 1 1

i made 2 restricted key figures; the 1st is volume restricted by the customer type a and the 2nd is also volume restricted by customer type b.

when running the report, the customer volumes fall in the right columns. however, each customer is counted in both sub column totals.

type a type b

(1-10) (11-20) tot (1-10) (11-20) tot

cust x 1 1 1

cust y 1 1 1

your advise will be most appreciated.

thanks.

[Former Member · Accepted solution]

Hi Jay,

How you are restricting..? With range or Less than..?

You can try this...

Create a formula: Typea : (Volume > 0 and <= 10 ) + (Volume > 10 and <= 20 )

Use boolean operations... only one of the above conditions will be right you can see correct results.

Hope it Helps

Srini

[Former Member · Accepted solution]

hi venkata,

i already sent you the screen capture of the query result

thanks

jay

-

---

hi srini.

i already made the formulas as you described. and the volume is falling in their respective columns according to customer type.

the problem is that every customer is incremented in both count columns of both customer types (e.g. a customer who belongs in the 'customer type a' column is also counted in the 'customer type b' column).

thanks

jay

[Former Member · Accepted solution]

Can you tell me how are you getting the count of '1' under the Act_Minimum column?

You can create a formula for Act_Minimum in the selection and use Boolean logic. You can say if the Act_Vol column “not equal to 0” then 1 else blank

Similarly you can have one more formula for Est_Minumum where you can use Boolean logic say if Est_ Vol “not equal to 0” then 1 else blank

Hope I have clarified your query.

If yes please assign reward points in SDN

[Former Member · Accepted solution]

hi venkata,

i made a restricted figure 'Act_Vol' (volume restricted by customer_type a). i also made a formula, Act_Minimum ('Act_Vol <= 10').

notice that in the first line in the file i sent you, the first contract account has a bill reading type of 'a'. so it's consumption of '5' falls under the 'Act_Vol' column and a count of 1 falls under the column 'Act_Minimum.

notice however that for the same contract account, 1 is also incremented under the column Est_Minimum which should not be the case since every account should only be counted either under customer_type a or customer type e.

hope i clarified things up a bit.

thanks

jay

[Former Member · Accepted solution]

hi venkata and srini,

i think i found the answer.

consider the formula Act_Min ('Act_Vol <= 10'). i changed the formula to

Act_Min ('COUNT ( 'Act_Vol' ) and i got the results i needed.

Thanks to both of you for the replies.

regards

jay

[Former Member · Accepted solution]

Dear Jay,

I think in the formula Act_Minimum should be ('Act_Vol' <= '10', '0'). which means if the condition Act_vol <= 10 is met/true the result will be 1 else it will be 0.

please try this and update me.

Regards

Venkata Devaraj

[Former Member · Accepted solution]

good,

please assign reward points.

Regards

Venkata Devaraj

[Former Member · Accepted solution]

hi venkata.

it appears that my findings were premature.

changing the formula to Act_Min ('COUNT ( 'Act_Vol' ) only counted each contract account according to their respective customer_types ('a' or 'b').

which only brings me back to square one; since i have to count each contract account's consumption according to their respective ranges (e.g. col1 = 0 to 10, col2 = 11 to 20, etc).

i tried using the formula you suggested ('Act_Vol' <= '10', '0') but there appears to be an error in the formula syntax.

thanks again.

jay

[Former Member]

unvalidated result

[Former Member]

Try the formula ('Act_Vol' <= '10', Act_Vol * '0').

regards

Venkata Devaraj

[Former Member]

hi venkata.

same error message pops up.

regards

jay

[Former Member]

hi venkata.

i tried this formula based on something i read from the sapnet document;

(Act_Vol <= 10) * 1 + (Act_Vol > 10) * 0

however the same error occurs, a customer with cust_type = a is counted in both the cust_type = 'a' and cust_type = 'e'.

when in fact, that customer should only be counted in cust_type = 'a'.

thanks.

[Former Member]

send me a copy of your excel file...i think i have the answer barnis@gmail.com

[Former Member]

hi william

excel file sent

thanks

jay

[Former Member]

Hi Jay,

Can you send the screenshot or excel dump of the report format/coloumns you need for a better understanding.

my email is devaraj_venkata@satyam.com

Regards

Venkata Devaraj

[Former Member]

hello again. any advice on how to do this?

thanks so much.