topic Count with only the last day in QlikView

Count with only the last day

Wed, 29 Feb 2012 17:15:56 GMT

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

Count with only the last day

Thu, 01 Mar 2012 17:29:04 GMT

Any idea?

Re: Count with only the last day

Thu, 01 Mar 2012 20:21:21 GMT

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?

Count with only the last day

jagan — Fri, 02 Mar 2012 05:51:42 GMT

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.

Re: Count with only the last day

Tue, 06 Mar 2012 10:57:22 GMT

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!