Help needed with Pick() function

kdmarkee — Thu, 06 Apr 2017 14:20:41 GMT

Based on some messy requirements primarily involving different granularity and Account Note Type, I am trying to get the first note of the day (First Note chart, shown below). Counts from the First Note Chart will match up with the total count of the 2 charts below it.

This First Note chart appears to work fine until I start making selections on Account Note Type, in particular the selection of "Account Note", then it breaks. I should see the number of notes in the First Note chart as I do in the Accounts Worked chart, but that is not always the case, as seen below. I would like it to pull out the Account Notes from the raw data.

Here are some key pieces of the First Note chart:

(1) I'm using this calculated dimension with a Pick() statement where I'm trying to assign priortiy in terms of how to define what notes make it to the First Note chart.

I'm either not tackling this the right way altogether, or my very last item for AcctNoteFlag is not doing what I think it should be doing.

=If(ExcludeNoteFlag='No',

Pick(Dim2,

GuarNoteFlag,

InsFuNoteFlag,

IncNoteFlag,

SLineNoteFlag,

if(Aggr(count(distinct{$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}[Account Note Type]), [Account Note User], [Account Note Date], [Guarantor Account])=1

AND Aggr(min({$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}GuarNoteFlag), [Account Note User], [Account Note Date], [Guarantor Account])<>1

AND IsNull(Aggr(min({$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}IncidentNotePriority), [Account Note User], [Account Note Date], [Guarantor Account])), AcctNoteFlag)

)

(2) Then I'm using Dimension Limits on the field using the Pick() above and restricting to the First 1.

(3) My expressions are then using FirstSortedValue() based on the Account Note Time in order to get the first note of the day based on the requirements.

Attached is my qvw which shows the raw data of course, but also shows the 3 charts in the images above. Any help on this would be super, thanks.

Message was edited by: Kris Markee (attached qvw)

topic Help needed with Pick() function in QlikView

Help needed with Pick() function