Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any idea?
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?
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.
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!