Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use function in set analysis

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

6 Replies
fabienmd
Partner
Partner

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


talk is cheap, supply exceeds demand
christophebrault
Specialist
Specialist

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable

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.

qvissue.png

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.


talk is cheap, supply exceeds demand
Not applicable

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

qvissue2.png