topic Re: Use function in set analysis in QlikView

Use function in set analysis

Wed, 04 Sep 2013 08:29:03 GMT

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

Re: Use function in set analysis

fabienmd — Wed, 04 Sep 2013 08:44:54 GMT

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])

Re: Use function in set analysis

Gysbert_Wassenaar — Wed, 04 Sep 2013 08:46:34 GMT

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])

Re: Use function in set analysis

christophebrault — Wed, 04 Sep 2013 08:47:16 GMT

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

Re: Use function in set analysis

Wed, 04 Sep 2013 09:10:04 GMT

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

Re: Use function in set analysis

Gysbert_Wassenaar — Wed, 04 Sep 2013 09:13:53 GMT

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.

Re: Use function in set analysis

Wed, 04 Sep 2013 09:57:41 GMT

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