topic Re: show values per key date / no cumulation in QlikView

show values per key date / no cumulation

Tue, 24 Apr 2012 13:40:42 GMT

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

Re: show values per key date / no cumulation

christophebrault — Tue, 24 Apr 2012 13:54:32 GMT

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

show values per key date / no cumulation

Tue, 24 Apr 2012 14:11:30 GMT

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 =(

show values per key date / no cumulation

christophebrault — Tue, 24 Apr 2012 14:15:07 GMT

try :

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

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

show values per key date / no cumulation

Tue, 24 Apr 2012 14:37:33 GMT

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)

show values per key date / no cumulation

Tue, 24 Apr 2012 15:05:25 GMT

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 =/