4 Replies Latest reply: Mar 6, 2012 5:57 AM by aaad sfds RSS

    Count with only the last day

    aaad sfds

      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
          aaad sfds

          Any idea?

            • Re: Count with only the last day
              aaad sfds

              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 mohan rao appala

                  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
                      aaad sfds

                      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!