1 Reply Latest reply: Sep 7, 2017 6:48 AM by Thomas Le Gall RSS

    Advanced Filter into Report using Aggr Or Set Analysis

    Andre Oliveira

      Hi Guys, hope you can help me!

       

      My problem it's a little crazy and I'll try give you the better explain that I could.

       

      Look, I have 4 tables (called - Conta / NPS / OS / PESQ) all tables are connected through a Syn Table (AutorizadoId) by the given field DataUnica.

      relacao.JPG

      DataUnica (it's a field the group all date field on each table and convert into unique date, for user filter a specific date on report and all tables will show values on the same range of dates)

      AutorizadoId (It's a key field that show what company do that service order, receive surveys and etc...)

       

      Pesq Table = a table that contains for each AutorizadoId have two groups of surveys one called Pesquisa (by who) and the other one called Avaliação (team name) so, example:

      AutorizadoId | Pesquisa | Avaliação | DataUnica

      xd123 | Gauge | SI | 01/07/2017
      xd433 | Track | RNSA | 01/07/2017

      xd123 | Track | SI | 01/08/2017

      ...

       

      Its a table with some companies with information what group they belonged in that month. This info it's necessary cause I need show to the user the historic of our KPI officially reported in that specific date. So because that I create the synthetic table with DataUnica cause this only will show me companies that belonged to the specific month of the filter of the user. But the question it's when user don't put any filter, when this happens I need to show only the companies that belong to the newest date in this table.

       

      For do this, I need a aggregation cause Set Analysis only work with aggregation fields (sum, count, max min etc..) And I need all this in a Filter: example:

       

      avaliacao.JPG

      The user filter the:

      Yerar = 2017

      Month = Jul and Aug

      Avaliação = SI

       

      so my report need bring to the user the companies belong to max(DataUnica) and belong to SI group.

      How I do this?!

       

      I've tryed the follow (into Avaliação Filter Pane): Aggr(NODISTINCT MAX(DataUnica), Avaliação) and the result was:

       

      result.JPG

      And I need the group RNSA or SI in this filter. How I can make this work? I need the RNSA and SI group always but If exist more than a single month I need load only the newest companies in the PESQ Tables, and If a range or YTD date, load the newest group.

       

      Help me! please!!!!!