topic Re: Urgent aggr help in QlikView

Urgent aggr help

Fri, 15 Feb 2013 19:00:14 GMT

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:

Look only at records whose service date is within the defined date range.
Find the earliest service date for each member (referred to as the index date)
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 — Sat, 16 Feb 2013 10:16:17 GMT

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

Sat, 16 Feb 2013 16:14:05 GMT

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 — Sat, 16 Feb 2013 18:39:03 GMT

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

Sat, 16 Feb 2013 19:14:13 GMT

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.

Re: Urgent aggr help

swuehl — Sun, 17 Feb 2013 13:36:07 GMT

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.

Re: Urgent aggr help

Gysbert_Wassenaar — Sun, 17 Feb 2013 14:09:44 GMT

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!

Re: Urgent aggr help

Sun, 17 Feb 2013 20:11:11 GMT

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

Thanks Stefan and Gysbert for all of your help!