on 2008 Aug 01 5:56 PM
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 ProductsProducts Sold
Percentage sold
Shop A / Jan
40%
Shop B / Jan
100%
Shop C / Jan
30%
TOTALS / Jan
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 ProductsProducts Sold
Percentage sold
Shop A
220%
Shop B
454%
Shop C
150%
TOTALS
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?
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.........
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
20080101
40
Edited by: Pedro Olvera on Aug 1, 2008 12:21 PM
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.
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'
User  Count 

66  
8  
8  
6  
6  
6  
6  
6  
5  
5 
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.