Problems with Key Figure and Drilldown

[Former Member]

Good day to you, experts. Several of our reports are required to include a key figure which shows a percentage value. The Query must show something similar to the following (please ignore the "-"):

-

---

Total Products---Products Sold--

---

Percentage sold

Shop A / Jan--

---

50

---

20--

---

40%

Shop B / Jan--

---

28

---

28--

---

100%

Shop C / Jan--

---

100

---

30--

---

30%

TOTALS / Jan--

---

178

---

78--

---

56.66%

We can see this information for all months. As you can see, it shows the "Shop" Characteristic as well as the Calendar Month (or year, or year/month). The information is updated monthly. The percentage total is calculated as average.

So far so good. The problem is that whenever I take away the Calendar Month characteristic (in order to see this information summarized and not detailed by month), the "Percentage Sold" Key Figure (which is loaded along with the rest of the information, not calculated in the Query) shows strange data, something like this:

-

---

Total Products---Products Sold--

---

Percentage sold

Shop A--

---

250

---

140--

---

220%

Shop B--

---

108

---

100--

---

454%

Shop C--

---

466

---

180--

---

150%

TOTALS--

---

824

---

420--

---

274.66%

So as long as I keep the most detailed information on the table, it is displayed correctly, and as soon as I take away any of the details (it also happens with the Shop characteristic), the percentage sold displays wrong data. I have tried displaying individual values differently. I also tried using a formula to calculate the percentage sold, which appeared to solve this issue, but after testing it I found that the data on "TOTALS" was incorrect both with and without the monthly detail. This issue has become quite urgent as we are supposed to publish these reports very soon.

Any ideas for solving this issue?

[Former Member]

We stil did not get how you are calculating percentage sold..can you detail with a sample data record and the how percentage calculation is done in the report.........

[Former Member]

Hi, thanks for the quick reply. I forgot to mention that. As I wrote before, this percentage is given to us directly in the file that is loaded on the system, but since there were problems when taking away a characteristic from the table, I created a formula which should give us the same data. The formula is Percentage = "Products Sold / Total Products" (I used the Percentage part operator, so it looks like this "Products Sold %A Total Products"). The TOTALS value is validated with the person in charge of giving us the file to load.

A sample data record:

DATE--

---

SHOP_ID-DEPT-PRODUCTS-SOLD---PCT.

20080101--

---

1

---

2

---

50

---

20--

---

40

Edited by: Pedro Olvera on Aug 1, 2008 12:21 PM

[Former Member]

Hi pedro,

In the key figure definition, for aggregation use 'aggregation after calculation.

Basically whats happening is

20/ 50 is 40%

30/50 is 60%

if you add then:

50/100 is 50%

but if it is before aggregation, then it will be

40% + 60% = 100% which is not what u want.

[Former Member]

Hi Raj, I have a question about the "aggregation after calculation". Can that be done in restricted key figures, or only on calculated key figures? Basically I would like to know if the Percentage Sold key figure (the one that is loaded in the system) is of any use or if what I am looking for can only be achieved by using calculated key figures.

Oh and one more question. ¿What happens if I wish to calculate the results using a different formula than the one used to calculate the percentage sold? What I mean is this: Percentage Sold = Products Sold / Total Products * 100. So this applies to the calculated key figure's values and results. Is there a way to calculate the results as "the Sum of all individual values of the key figure / number of records"? I tried "average" in the property "calculate results as..." but the results are incorrect.

[Former Member]

are u on 3.5 or 7.0?

[Former Member]

Sorry again, I also forgot to mention that. I am on 7.0

[Former Member]

Since you are on 7.0 ignore 'aggregation before calculation'

Percentage Sold = Products Sold / Total Products * 100.

I think is a better option.

instead of just 'Average' - try 'Average of all values that are not Zero. Null or Error'.

And also try checking the option there 'Cumulated' and apply to 'Results'

[Former Member]

Hi, thanks for the feedback. I have applied your suggestion and will check the results displayed with the people who can tell me if this solution is displaying the correct results. I will then come back and post how things went. This will probably be until monday or tuesday.

[Former Member]

The problem was solved by creating a calculated key figure with this formula and defining its aggregation as "average" based on a time characteristic. Thanks for your susggestions Raj, I have assigned points.