Drill-down in Dimensional Limits

[#1]

I am making a bar chart showing expenses every month. On the first level I want to show all 12 months chronologically and against total expense. At the drilldown, I only want to show the top 3 types of expenses for that month. Dimensional limits either lets me show all 12 but then shows 12 expenses, or shows the top 3 months and then the top 3 expenses.

Is there a way to separate the drilldown dimensional limit from the first level dimension?

I really appreciate any thoughts!

[rustyfishbones]

Try using the RANK() function in the Dimensional limits

[#3]

Hi Alan,

I see the checkbox that says "Restrict to 3 Largest" but would get me back to the problem of only showing 3 months and 3 expenses. Is there a way to write a rank function into the dimensional limits while restricting the values to 12 so that all the months are shown? Thanks!

[rustyfishbones]

Is it possible to send me a copy of the file, I will try figure it out in the meantime

[#5]

I'm unfortunately not able to post the full file (confidential info! ). Let me give a concrete example below:

Monthly expenses are as follows:

Jan               1000

Feb               2000

March           1500

April              1400

May               2500

June              2400

July               1575

Aug               2100

Sept             1300

Oct               1900

Nov               1800

Dec               2010

If I drill down into January, I find that there are 20 expense categories that sum up to $1000 (rent, electrical, operations, IT support, labor, etc)

The top three expenses are

Rent - $400

Legal - $175

IT - $100

Would I be able to create a bar chart that shows all the months with total expenses, and then a drill down into Jan that would just show four bars - Rent, Legal, IT, and Other ($325)?

Please let me know if that's clear. Thank you!

[rustyfishbones]

Hi,

I understand not what you are trying to achieve, let me try figure if it can be done

Regards

Alan

[prabhuappu]

Hi,

use the following expression in the dimensions limit value,

=if(GetCurrentField(Group_Name)='expense categories',4,12)

Group_Name is drill down group name.

Regards,

Prabhu