Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Urgent aggr help

Hello,

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

bretwalker,

I believe you need to apply the NODISTINCT qualifier to your aggr() function.

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

Please check also attached.

View solution in original post

7 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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.

swuehl
MVP
MVP

bretwalker,

I believe you need to apply the NODISTINCT qualifier to your aggr() function.

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

Please check also attached.

Gysbert_Wassenaar

Ahaaaah, so that's why there's a nodistinct qualifier for aggr. I was very much in the dark about that. Thank you so much Stefan for enlightening me!


talk is cheap, supply exceeds demand
Not applicable
Author

From my testing, I believe this is exactly what I was looking for.

Thanks Stefan and Gysbert for all of your help!