Input variable

[#1]

Hi all,

Please find the attached application. I want to include variable in Pivot chart.

Help me to solve this.

Thanks & Regards,

Usha

[its_anandrjs]

Hi,

Use Rank function to find Top Sales member.

Rgds

Anand

[Miguel_Angel_Baeyens]

Hi Usha,

The easiest way to get this done in a straight table (not using any complex aggregation and let the chart do it by itself) is:

• go to the chart properties, Sort tab, promote "Sales" and sort only Numeric Descending.
• go to the Presentation tab, check the "Max Number (1-100)" and set there the variable

=VTopSales

• Uncheck the "Show Others Row" and you are done.

The more complex and poorer in performance (but needed in some cases) is to create a calculated condition using Aggr() and Rank() to get only those that meet some criteria. The Calculated Dimension would look like this

=If(Aggr(Rank(Sum(Sales), 4), [Emp No]) <= VTopSales, [Emp No]) 

and click for this new dimension the checkbox "Suppress when value is null", that will not show the "others" value, only the top X values. In this second case you don't need to limit the chart or sort it.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[its_anandrjs]

Hi Usha,

If you use straight table so easly you are able to display Top Sales. I use your variable vTopSales on input box if you enter values the value check in straight table and according to value Max number is visible by setting properties in Presentation -> Max Number -> type variable name there like =VTopSales.

Any reason to use pivot table.

See the attached sample file.

Let me know about this.

HTH

Rgds

Anand