How to set up dynamic monthly counts in "text in chart" field

[#1]

Our client requested the following text on the top of a chart:

Feb 2013 (n=100)  Mar 2013 (n=200)  Apr (n=300) ... up to the last month

I would really like to set that up dynamically like it is possible in a list box by MYear with calculated expression

=' (n='&Count(MYear)&')'

MYear is calculated on the load like

LOAD MonthName(Monthstart(date(DATEFILED))) as MYear

Any help is greatly appreciated

Thank you very much!

[er_mohit]

Try this

=subfield(getcurrentselection(),':',-1)&' '&' (n='&Count(MYear)&')'

[#3]

in script-

LOAD MonthName(Monthstart(date(DATEFILED))) as MYear

month(date(Datefield)) as month

then use this

= 'n= ' & count(if(Datefield>=MonthStart(max(Datefield) and Datefield<=MonthEnd(max(Datefield)),MYear))

[#4]

I got

(n=5022)

which is the total number for 6 months

[stigchel]

I think the 'text in chart' expression is not evaluated over the dimension. It would be easier if you just added an expression in the expressions tab (=' (n='&Count(MYear)&')') and deselect the line/bar and select the text on axis option. (I'm assuming MYear is your dimension)

[#6]

That expression does not return any results. I really believe that aggr function has to be involved.

Thank you anyway!

[#7]

This is a really good approach, but I have another dimension beside MYear and counts really get messed.

Could be good for other charts

Thanks a lot

[stigchel]

Ah, you have a second dimension....To prevent the messiness (multiple texts are plotted over each other) use this:

     if(rowno()=1,YourExpression,'')

[#9]

This is the best approach I got with the following expression:

= if(RowNo(TOTAL)=1,MYear&' (n='&aggr(Count(MYear),MYear)&')','')

I get the right number for the first month, but cannot see other months.

When I do not use TOTAL,

= if(RowNo()=1,MYear&' (n='&aggr(Count(MYear),MYear)&')','')

I get :

[stigchel]

I'm afraid I haven't been looking very good at your dimensions. Normally I use this technique where the periods are the first dimension, the Rowno()=1 is there to display only for each first value of the first dimension (make an excel export to see what I mean). The expression is also without aggregates as I do not need them (the dimensions are doing the aggregations) and the base values are displayed to the corresponding dimension values.

In your case that would mean displaying the base for each value in the period 5 times, for which there is no room. Unless offcourse you change the display of the labels to vertical (in propertiestab axes). Not very nice but you also can't display the months bases for each value of the other dimension as there are only five and you have six months