Calculated Key Figure Issue

[Former Member]

Hi All,

can anybody please help me with this issue regarding calculated key figures.

iam busy doing a report in this format

Block1 Block2 Block3

no. of consumers1 quantity1 no.of consumers2 qunatity2 no.of consumers 3 quantity3

calendar month:

calendar month in rows and no.of consumers and quantity of different blocks in columns.

here in Block 1, i have two calculated key figures based on cube keyfigure quantity and a counter for no. of customers.

no. of consumers1 = ( ( ( quantity > 0 ) * ( quantity < 50 ) )* (no.of customers) )

Quantity1 = ( ( ( quantity > 0 ) * ( quantity < 50 ) )* ( quantity ) )

Block 2

no. of consumers2 = ( ( ( quantity > 50 ) * ( quantity < 100 ) )* (no.of customers) )

Quantity2 = ( ( ( quantity > 50 ) * ( quantity < 100 ) )* ( quantity ) )

Block 3

no. of consumers3 = ( ( ( quantity > 100 ) * ( quantity < 500 ) )* (no.of customers) )

Quantity3 = ( ( ( quantity > 100 ) * ( quantity < 500 ) )* ( quantity ) )

my problem is, the rows for individual months is not populating data for columns, but at the same time if i add customer number in rows after calendar month, the colums values are populating, but sum for that respective calendar month is not adding up and other columns giving funny numbers.

i used CKF's with "Calculate Result as " Summation and nothing defined but no use.

and time of calculation as " after aggregation" and used other options as well but no use.

i will appreciate your inputs.

Regards

Robyn.

[Former Member]

Hi,

any inputs??

Regards

Robyn

[Former Member]

Hi Experts,

I want to make this simple, if i was not clear in earlier post. The essence of this exercise is to find out number of customers, and the quantity they used in three range values ( based on Quantity(KF)). per month. Month has a variable to select single or interval to see these values for those months.

range1: Quantity between 0 to 50.

range2: Quantity between 51 to 100.

range3: Quantity between 101 to 500.

I tried using CKF's as mentioned in above post, but the result comes as 0's for all months, even though there is data. but if i report to lower lever like month and customer no. in rows, and using display hierarchy i can see values at customer no. level for all these values. but its not summing up for that month. but my requirement is not to show customer no as it becomes too big.

Please give your opinions how we can achieve this.

Best Regards

Robyn.

[Former Member]

Hi Robyn

If I understand you correctly, you are have data as follows in the infoprovider:

division Customer Quantity

10 C1 25

10 C2 30

10 C3 75

10 C4 120

10 C5 400

10 C6 499

You want to see the results as follows:

Division Cust_cnt_0_to_50 Cust_cnt_51_to_100 Cust_cnt_101_to_500

-

---

-

---

-

---

-

---

10 2 1 3

If the above is what you want, you just need to create formula or CKF with following syntax:

CUSTCNT_0_TO_50:_

( QUANTITY <= 50 AND QUANTITY > 0 ) * 1 + 0

CUSTCNT_51_TO_100:_

( QUANTITY <= 100 AND QUANTITY > 50 ) * 1 + 0

CUSTCNT_51_TO_100:_

( QUANTITY <= 500 AND QUANTITY > 100 ) * 1 + 0

The above syntax for the formulas/CKFs will give you the result you want.

Cheers

Vasu

[Former Member]

Hi Vasu,

Thanks for the reply, you got that almost right, but the situation is slightly different what if:::

division Customer Quantity

10 C1 25

10 C1 15

10 C1 10

10 C2 30

10 C3 75

10 C4 120

10 C5 400

10 C6 499

in this case

Division Cust_cnt_0_to_50 Cust_cnt_51_to_100 Cust_cnt_101_to_500

10 4 1 3

0r

10 2 1 3

another issue is the result line is showing 0's like

10 -

---

0 -

---

0----

---

0but if i include customer in rows it's showing values for these counts. like

10--

---

C1 -

---

1

---

0--

---

0

my requirement is to give figures by calendar month, if i include customer in rows its fine but i can't show all customers for all months that makes report too big, and performance will b terrible.

i will appreciate any feedback

Best Regards

Robyn.

[Former Member]

HI Vasu,

Thank you for your formulas, they did work.

but now the problem is:

Division Cust_cnt_0_to_50 Cust_cnt_51_to_100 Cust_cnt_101_to_500

i need the result in this format, but the result is all zero's, but if i put

Division,customer, Cust_cnt_0_to_50,Cust_cnt_51_to_100, Cust_cnt_101_to_500.

it's giving me the result correctly all in correct columns. query reult, overall result is correct too.

lets say i just need results for divisions without customer numbers.

may be beacuse the quantities we are checking in blocks is lower level by customer , and the division is higer level, the calculations are not met. workaround is to display division & customer in rows and display as hierarchy but the total at divsion level, should just be the sum of all its customers rather than calculation at division level.

Thanks

Robyn.

[Former Member]

Hi,

Does anyone has any clue why is it behaving like this.

Regards

Robyn.