Load only 50 top sales items

[#1]

I all.

This is my first post!

I have a QV document with a script that already load all the product (items) of my company, and each sales amount.

Now I have to find the way to create a pie chart with the 50 top sales items.

The problem is that some items have negative amount and pie chart only display a message like: "Positive and Negative values in the chart".

How can I do to avoid this problem?

I tried with this formula:

if (SUM (if ((ANNO_ANALISI=Anno_Exp and MESE_ANALISI<=Mese_Exp), sales_amount/1000))>0,

SUM (if ((ANNO_ANALISI=Anno_Exp and MESE_ANALISI<=Mese_Exp), sales_amount/1000)), 0)

where ANNO_ANALISI and Anno_Exp are temporal selections and are ok.

With this formula I obtained the perfect chart, but the calculation time it's very high (some minutes!!!)

Thank's for any answer!

Barbara

[#2]

If I understand your requirement correctly, you need to exclude the negative sales amounts. The selection made a reflected in the "IF" clause.

First of all, you need not ue "IF" clause to include selections since its taken care by QV. Thats leaves with the negative amounts which are easily dealt using SET ANALYSIS. Hence the expression goes as below:

SUM({<sales_amount={">0"}>} sales_amount).

To limit to top 50 if you are using QV 11, it can be done with "Dimension Limits" tab of chart properties.

-Kiran Rokkam.

[#3]

Thank you very much Kiran,

now I tried to explaine why I can't exclude negative value before the sum.

Some items can have some negative detail value (in some invoice for example), but my purpose is to have the sum of the sales by items. Then some negative detail can takes part in the total.

My need to eliminate negative sum (and not negative detail for the sum), it's only to display the chart.

Then I think to have 2 solutions:

1) to load only 50 top sum of sales amount by item (that surely non include negative value)

  ---> but I don't know how to do

2) to exclude only negative SUM from the chart.

  ---> but my formula takes a long time to calculation

I hope my explanation it's better than before.

I'm sorry if not.

Thank you very much!

[Anonymous]

I always use:

=if( Sum (Sales) <0  ,0,  (Sum (Sales) )

In a pie charts.

[#5]

Hi,

Dennis solutions to avoid negative values is best option. For getting top 50:

Go to Chart Properties->Sort and pick "Y-Value Descending".

If you use QV 10: Go to presenatation in chart properties and set Number of slices to 51.

If you use QV 11: Got to dimension limits and set  limits to show only the largest 50.

-Kiran Rokkam.