7 Replies Latest reply: Feb 17, 2013 3:11 PM by bretwalker RSS

    Urgent aggr help



      I have an urgent problem that I just cannot figure out.



      I have a list of members who have service calls. So one member may have several service calls on multiple dates.

      The records also have pre- and post-day counts which indicate how long the person was a member before and after the service call date.


      I need to be able to allow the report users to set a date range to use and then filter out any members whose *earliest* record is does not have a specified number of pre- and post-days.


      The logic should work like this:

      1. Look only at records whose service date is within the defined date range.
      2. Find the earliest service date for each member (referred to as the index date)
      3. If that member's service date is not proceeded for followed by the specified number of pre- and post- day counts, exclude it


      I've tried the following set analysis, but it doesn't work:


      count (distinct { $< POST_DAYS = {">=$(vPostDays)"},   PRE_DAYS = {">=$(vPreDays)"},   MBR_DATE = {"=MBR_DATE=Aggr(MinString(MBR_DATE),MBR_ID)"> } MBR_ID)


      I've attached a sample QVW.


      One note: the actual data contains many more dimensions that I will need to allow users to use to filter, and it's not practical to pre-calculate every member's index  date, since the index date depends on what filters are applied.


      If anyone can help me solve this problem, I'd be very grateful.

        • Re: Urgent aggr help
          Gysbert Wassenaar

          Apart from a missing } your expression is correct as far as I can tell.


          count (distinct { $< POST_DAYS = {">=$(vPostDays)"},   PRE_DAYS = {">=$(vPreDays)"},   MBR_DATE = {"=MBR_DATE=Aggr(MinString(MBR_DATE),MBR_ID)" } > } MBR_ID)

          Something you might need to consider is that minstring could trip you up. 1.1/15/2013 is smaller than 1.1/2/2013. If your MBR_ID's are numbers you could use rangesum(MBR_ID*100000,num(date#(Date,'M/D/YYYY'))) to create a number field. That has the additional advantage you can use the min function which performs better than minstring. Calculationg with numbers is usually a lot faster than calculating with strings.

            • Re: Urgent aggr help

              Thanks for your help.

              Do you know why the code you gave might be affected by the load order?

              I tried changing the load order around to make sure it didn't affect the results, and when the records aren't ordered by Date, the expression doesn't return any results.


              See the attached file for an example.

                • Re: Urgent aggr help
                  Gysbert Wassenaar

                  No idea. I could reproduce it. It probably means we were trying something that can't/shouldn't be done like that and triggers calculations with undefined behaviour/results. The only thing that seems to do what I expect is this:


                  if(min({$<POST_DAYS=,PRE_DAYS=>}Date) = min({$<POST_DAYS={'>=$(vPostDays)'},PRE_DAYS={'>=$(vPreDays)'}>}Date),1)

                    • Re: Urgent aggr help

                      That seems like it's working in terms of filtering, but how do I use it to replace the count() expression?


                      Let's say I want to have a pie chart that shows counts of members of a gender. How would I do that? If I just use the if statement, it doesn't aggregate. So if I added a gender column and added another member with gender "F" to the inline table, the pie chart, without filters, would show 2 F and 1 M.


                      Thanks a lot for your advice, I feel like we're close to solving my problem!


                      EDIT: I think this might do the trick:

                      if(min({$<POST_DAYS=,PRE_DAYS=>}Date) = min({$<POST_DAYS={'>=$(vPostDays)'},PRE_DAYS={'>=$(vPreDays)'}>}Date),count(DISTINCT MBR_ID))


                      EDIT 2: That doesn't seem to work quite right.