Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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.
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.
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.
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)
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.
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.
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!
From my testing, I believe this is exactly what I was looking for.
Thanks Stefan and Gysbert for all of your help!