show values per key date / no cumulation

[#1]

Hi Guys,

hope you can help me with this:

I want to show our FTEs (Full Time Equivalent) per quarter (and/or year), which means my dimension is CalendarQuarterAndYear and my formula "sum(FTE_Number)" (which i can't reduce to "=FTE_Number"), but this way our average employees of 400 will be cumulated up to 1200 employees per quarter (or 4800 per year). What i want is that for lets say 1st quarter 2011, it shows the value per 31.03.2011. (I only have the values per end of each month!)

I'm sure it isn't that hard, but I haven't found anything concerning that, neither in the community neither in a demo-file.

Thanks a lot in advance,

Chris

[christophebrault]

hi,

if you have a field DATE like 31.03.2011 in your calendar, you can try in your expression :

sum({$<DATE={$(=MAX(DATE))}>}FTE_Number)

This set expression will give your FTE_Number for the max Date of each quarter in your graph.

Hope this can solve your problem

[#3]

Thank you for your fast reply,

unfortunately it didn't solve my problem,

i adapted your expression: sum({$<Datum={$(=MAX(Datum))}, FTEArt={Budget} >}FTE_Number)

Datum means Date in German (and I do have this field in my calendar), FTEArt means the type (Actuals/Budget).

As expression I chose "CalendarQuarterAndYear", but the chart is empty -> no values available =(

[christophebrault]

try :

sum({$<Datum={$(=MAX(Datum))}, FTEArt={'Budget'} >}FTE_Number)

If its still not working, i don't know...

[#5]

still not working =(

especially for headcount i can't imagine why no one else has/had this problem (as i wasn't able to find it within the community)

[#6 · Accepted solution]

okay, I got it by now, i told him to just use the months March, July, Sept, Dec, then it shows the correct value for each quarter =/