Count with only the last day

[#1]

Hello everyone,

I am facing up to this problem (qvw attached):

1) I have employees

2) I have dates (from 2009 to 2011)

2) I need to count these employees per year, month and day in a drill down

3) The difficulty is :

     a) For the year, I need to count only for the last day of the year (ex: for 2011 I count employees for 12/31/2011)

     b) For the month, I need to count only for the last day of the month (ex : for Feb-2012 I count employees for 29/02/2012)

     c) For the day, no problem

4) What I tried :

     a) To do a Set Analysis. The problem is that when I ask the max(date) it doesn't take care of the dimensions anymore (which is the normal rule for the Set Analysis). Actually, when I do that in my graph, it only returns the counting for the last day of my whole date field (12/31/2012)

     b) Do it in the script. The problem is that I loose my hierarchy because, for example, 2009 only points on 12/31/2009 so when I click on 2009 in my drill down, I will only select Dec-2009.

Maybe there is a way that the Set Analysis can take consideration of the dimensions but i don't know how.

Stay available for any more questions.

Regards.

Anthony

[#2]

Any idea?

[#3]

Thank you for your answer Marcus. Actually it's exactly what I am looking for. I have put a new column (see example) to explain it. As you can see in the qvw file, I have made it work for the first line and I need to do that for all the lines in dynamic.

Do you see what I mean?

[jagan]

Hi,

It would be easier if you arrive new fields like IsYearEnd and IsMonthEnd in script itself.  Please check the script below

Calendar:

LOAD _key_date,

    If(Date = YearEnd(Date), 1, 0) AS IsYearEnd,

    If(Date = MonthEnd(Date), 1, 0) AS IsMonthEnd,

     Date,

     year(Date) as Year,

     month(Date) as Month,

     day(Date) as Day,

     year(Date)&'-'&month(Date) as Period

FROM

data\data.xls

(biff, embedded labels, table is Calendar$);

Now create a drilldown group(assume the name as DateDrillDown) with Year, Month and Day.  Now use this as Dimension in you Chart.

Now your expression should be

=If(GetCurrentField(DateDrillDown) = 'Year', sum({<IsYearEnd={1}>}Activity),

If(GetCurrentField(DateDrillDown) = 'Month', sum({<IsMonthEnd={1}>}Activity), sum(Activity)))

Hope this helps you.

Regards,

Jagan.

[#5]

Thanks Jagan it's working!!! I just had a problem with YearEnd and MonthEnd because of the hours.

Ex : MonthEnd(06/03/2012) returns 31/03/2012 23:59:59.999!!

Even with numeric format it was not working so I only found this solution :

Calendar:

LOAD _key_date,

     If(Date =date(AddYears(YearStart(Date),1)-1), 1, 0) AS IsYearEnd,

     If(Date = date(addmonths(MonthStart(Date),1)-1), 1, 0) AS IsMonthEnd,

     Date,

     year(Date) as Year,

     month(Date) as Month,

     day(Date) as Day,

     year(Date)&'-'&month(Date) as Period

FROM

data\data.xls

(biff, embedded labels, table is Calendar$);

But thanks for the idea especially the GetCurrentField function that I didn't think to use!