Use function in set analysis

[#1]

Hi,

Is it possible to use a function in set analysis like the one below?

aggr(

date(max({$<

[Incident Log External Notes]-={''},

num(AddMonths([Incident Log Effective Date],3))<num(today())

> }

num([Incident Log Effective Date])),'DD/MM/YYYY'),[Incident Internal Id])

For some reason my query does not display any rows with the part in bold.

Goal: display incident records where the field 'Incident Log External Notes' is not empty for a log of an incident AND

the last available incident log (which has an external note) is older than 3 months.

Attached the QV document.

Regards,

Maarten

[fabienmd]

Hi,

Is this what you want ?

aggr(

date(max({$<

[Incident External Notes]-={''},

[Incident Log Effective Date]={"<=$(=MakeDate(Year(today()),Month(today())-3,Day(today())))"}

> }

num([Incident Log Effective Date])),'DD/MM/YYYY'),[Incident Internal Id])

[Gysbert_Wassenaar]

Try:

aggr(

date(max({<[Incident External Notes]={'*'},

[Incident Log Effective Date]={'<$(=date(addmonths(today(),-3)))'}

>}

num([Incident Log Effective Date])),'DD/MM/YYYY'),[Incident Internal Id])

[christophebrault]

Hi,

this expression gives you incident with external notes older than 3 month :

only({$<[Incident Log Effective Date]={"<=$(=AddMonths(Max([Incident Log Effective Date]),-3))"}>}[Incident External Notes])

To use it in your straight table you must add a column Date, incident ID

[#5]

Dear Fabien, G Wassenaar, Christophe,

Thanks a lot for your quick answers! Really appriciated.

I've tried all solutions and they all seem correct for what was the original issue.

Still two questions:

1) Is it correct that on the left hand side (the field) no functions can be used? (I see everybody only manipulates the right-hand side.

2) Any idea why for incident internal id 0910-0354 I don't get the correct date? (there are others too...). I suppose it has to do with some other part of my logic. I was expecting to see 23/10/2009 and not 22/05/2013.

Kind regards,

Maarten

[Gysbert_Wassenaar]

1. Yes, only fields are allowed on the left hand side in set modifiers.

2. You didn't use an aggregation function for Incident External Notes. In that case QV applies the only() function which will only return a value if there is only one value. If you use maxstring([Incident External Notes]) you'll see that there are in fact notes for that date.

[#7]

Wow, that's a function I did not know about. Very interesting!

Still, there seems to be a flaw. When taking a closer look at incident internal id 0910-0106.

27/05/2013 is indeed the first record that has external notes created more than 3months ago, but there are records present with a more recent log holding external notes.

Don't want the record to be listed in my table if there are more recent logs with external notes.

Don't know if I make myself clear. Maybe a picture will help