Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've attached a qvw that pulls the first note of the day based on complicated requirements into one chart and then I have other charts that roll up the data even more. I found a particular scenario where the "roll up" chart is not including all the counts/rows I expect. The first sheet of the attached qvw shows all the raw data and the requirements, and the second sheet shows what I'm trying to achieve and where my issue is. Can anyone see what I am doing wrong with my Accounts Worked expression? Thank you.
May be this
=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 Aggr(min({$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}[AcctNoteFlag]), [Account Note User], [Account Note Date], [Guarantor Account]) = 4
)
or
Aggr(Min({$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}[GuarNoteFlag]), [Account Note User], [Account Note Date], [Guarantor Account]) = 1
,
count(distinct{
$<
[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"}
,[Account Note Type]={'Account Note','Gua FollowUp Note'}
,[ExcludeNoteFlag]={'No'}
>}
[Guarantor Account])
)
I ran a text compare between what your expression syntax and mine because the difference wasn't obvious, only to find out they were the same. Did you maybe paste in my syntax by mistake, instead of yours? Thanks.
I can check in a little bit
You were right... may be this
=Sum(Aggr(If(count({$<[Account Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}, [include on first note tab]={'yes'}>}[AccountNoteCounter]) > 0,
IF([Account Note Type] = 'Gua FollowUp Note' OR [Account Note Type] = 'Account Note', 1, 0), 0),
[Account Note User], [Account Note Date], [Guarantor Account], IncidentNotePriority, NotePriority, GuarNoteFlag, [Account Note Incident ID], [Account Note Service Line ID], [Account Note Time], [Account Note Type], [Account Note], [include on first note tab], ExcludeNoteFlag))
Using [include on first note tab] won't work, as that is not a true field in my model; I only added that so I could have a way to build a straight table/chart to show me what I needed to achieve for that particular set of data. Sorry, I should have made that clear.
So what else can be used to determine include on first note tab?
After some more research, I was able to solve my problem with my roll up "Worked"chart by approaching it differently and not focusing so much on my "First Note" chart but have now found an issue with my "First Note" chart. I will supply a sample qvw and the outcome I am seeking shortly under a new discussion as I believe the problem is with my Pick() function in a calculated dimension (and this discussion was originally about Aggr(). Thanks.