Chart on the last 12 months

[#1]

Hello ,

I ve met an problem ,

I m trying to build an chart which calculate  an indicator (NBcustomer) on the last 12 months

Dimension : Month

Indicators : sum(NBCustomer)

I would like the last 12 months begin at the date of today() if no selection is done or or after when an month and an year is selecionned it s start at this month..

If you could help i ve to delevery in short time ...

Thank a lot

[Gysbert_Wassenaar]

See blog posts Relative Calendar Fields and Accumulative Sums and document Calculate rolling n-period totals.

[#3]

Hello,

Thank for the answer and for the link but i ve tried to use the expression but it doesn't work and i prefer use the set analysis and not transform my calendar :

Sum({$<Date={">=$(=MonthStart(AddMonths(MaxDate),-12)))"}*{""<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

Do you know what is wrong is this syntax...

Thank

[#4]

Try with this:

Sum({$<Date={">=$(=MonthStart(AddMonths(MaxDate),-12))) <$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

[#5]

thanks it s working but the problem is if i selected an month my chart is updating and i only see the month

cause my dimension is month

and i would like to know if you think if it s possible to fix the dimension month... and to only update the expression inside?

I don t know if it s understanding

thank

[#6]

Try:

Sum({$<Month=, Date={">=$(=MonthStart(AddMonths(MaxDate),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

That will disregard month selection for that expression only.

[#7]

thank it s working for the month but i don t know why the chart doesn 't display the month of the previuos year

And it s strange cause if i select year= 2013  and month =april for example it stop at januray of 2013

i don t have the month of the 2012 ....

Have you any idea what is the origin of this problem ?

Tank a lot

[#8 · Accepted solution]

I think there was an error in the formula. Try:

Sum({$<Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

[#9]

thank a lot , it s working if i want put an  hierarchical dimension (month, week ) you think it s possible or i can t in the chart cause if i select an month my hierarchical go to the week and do you know if i can sort an hierarchical dimension and how ?

i ve a lot of question i know i m not an expert  but thank a lot for your help

[#10]

I don't think there is a problem about using a hierarchical dimension, but you must realize that if for example, you have a Month - Week dimension, when you select a Month, the expresion will take the max date for the selected month and the dates interval will change and show all the weeks that exist in the new set of dates according to your set analysis expression.

Also you'll have to check if you want to disregard the Week field selections, since if you select a Week the current set analysis won't disregard this selection and will only show information for that specific week. In order to disregard Week field selections you must add "Week=" to the expression.