Average line on bar chart for current month on current year and last month on previuos year

[#1]

Hello everybody!!

I have this problem:

I have a bar chart, this chart displays the sum of all the sales per month and I need a line with the average sales of all the months displayed.

This is for current month:

And using reference line on presentation tab with this expression shows ok:

=Num(Sum(FactTotVenMen)/$(MesActual),'#,##0')

the MesActual variable show the current month in number Aug = 8

How can i set this expression to use the last month on the cart? for example, if the last year show info until December, the expression has to be:

=Num(Sum(FactTotVenMen)/12,'#,##0')

or if the current year month is October:

=Num(Sum(FactTotVenMen)/11,'#,##0')

Last year (bad average because it divide /8):

Thank you!!

[cesaraccardi]

Hi Jorge,

I think you can use an expression to calculate the maximum month of the past year, so you could use set analysis to filter for the past year if you have a field with the year information. Something like:

=max({$<Year={2012}>} Month)

If you need this to be lesser than the actual month you can add a set modifier for the Month field as well:

=max({$<Year={2012},Month={'<=$(=Max(Month))'}>} Month)

Hope it helps you,

Cesar

[#3]

Thank you Cesar,

But how can I calculate the last month on the selected year, for example, on 2013 i have to divide /8 (current month) and incremental every month /9 = september /10= october, and for the last year the last month= /12

I have two data on my load statement:

FactAnioVenMen: Year

FactTotVenMen: The sum of all the sales of the selected year

Thanks!

[cesaraccardi]

Hi Jorge,

Assuming you have the current year selected you can use "=max(Month)" to give you the last month of the year however if you need the last month with data you could just add the modifier for FactTotVenMen as well:

=max({$<FactTotVenMen={'>0'}>} Month)

Regards,

Cesar

[#5]

Thank you Cesar,

Maybe my information is malformed, if I use =max(FactVenMen)  (FactVenMen is the table where my months are saved) I just get nothing. Same result with =max({$<FactTotVenMen={'>0'}>} FactVenMen)

If I use the Trendline on Expression tab, it works fine but it does not show the value of the average.

Thanks for your help.

[cesaraccardi]

Hi Jorge,

I see FactVenMen is in a text format so it will not work with "Max", do you have a field for the Month in a numeric format? If so, try to replace it.

Regards,

Cesar

[#7 · Accepted solution]

Finally, after rebuild the DB to store dates as dates, I have done with this:

Reference line:

='Promedio: '&Num(Sum(MontoMensual)/num(month(max(FechaFullMensual))),'#,##0')

MontoMensual sums all totals

FechaMensualFull is the dates of the invoices on YYYY-MM-DD format.

Thanks everyone for the help on this.