5 Replies Latest reply: Mar 15, 2017 1:16 PM by Sunny Talwar RSS

    Using calculated dimensions to dictate what rows to display in straight chart

    Kris Markee

      I'm hoping someone can point out where I'm going wrong as I've spend a day on this and can't quite nail it down. 

       

      Here is the raw data I have:

      raw data.PNG

      My desired result is the following:  show the first note time with its corresponding type and text, grouped by user, date, account and incident, and then also include the number of notes at that grouping

      desired result.PNG

      my dimensions are: User, Note Date, Account, Incident, Type, Note Text, and 2 calculated dimensions…described below

      1)  Note Time - which is going to be first note time with this aggr statement, and the set analysis is just looking for notes withing within a date range set in 2 input boxes, and notes that ...

      Aggr(

      Min(

      {$<[Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}

      [Note Time]),

      [User], [Note Date], [Account], [Incident])

       

      2) show row flag - this is used to conditionally show the row by checking the dimension to Suppress When Value is Null, nad by setting the field on the Presentation tab to Hide

      Aggr()

      If([Note Time] = Min({$<[Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>}[Note Time]),'Yes'),

      [User], [Note Date], [Account], [Incident])

       

      AND my expression for note count is:

      Aggr(

      count(

      {$<[Note Date]={"$(= '>=' & vNoteStartDate) $(= '<=' & vNoteEndDate)"},[ExcludeNoteFlag]={'No'}>} [NoteCounter]),

      [User], [Note Date], [Account], [Incident])

       

       

      But for some reason I sometimes get this as the result and I don't know why, where the yellow cells are null:

      bad result.PNG

       

      Any advice on what to try would be greatly appreciated.  Thanks in advance.