5 Replies Latest reply: Nov 21, 2013 10:48 AM by Nicole Smith RSS

    Trying Analysis like for each

    German Avanzato

      Hi Everyone:

      I´m trying to achieve something through Set Analysis but I dont know if this is the best way to do it.

      First, I will put my scenario.

       

      I have a sales fact table. The important information looks like this:

       

      YearMonthSalesManClient
      20131ACli-A
      20132ACli-A
      20133ACli-A
      20134ACli-A
      20135ACli-B
      20136ACli-B
      20131BCli-B
      20132BCli-A
      20133BCli-C
      20134BCli-C

       

      What I´m trying to achieve, its that after the user select a Year, Month in the Dashboard, the Dashboard calculates the last 6 month from the select Year / Month, and show per Salesman the numbers of visits that they made with the clients.  Its count one visit if in the month they have at least one sales. If thay have more, It doesn't matter.

       

      Also, this visits must be classified in ranges (like 6 visits in the last 6 month, count 1 for Excelent visits , 5 visits in the las 6 month, count 1 for Good visits , and so on).

      With the example data, the final result in a table looks like

       

      Total visits Excelent (6 visits )Good (5 visits )Average (4 visits )Regular (3 visits )Bad (2 visits )Very Bad (1 visits )
      2001010
      3000012

       

      Total Contacts: the total numbers of unique contacts that the Salesman did. Its resolve with a count distinct (Client).

      For the SalesMan B; he make 1 visits in the last 6 month to the client Cli-B (count 1 in the column "Very Bad") and 1 visit to the client Cli-C (count 1 more in the column "Very Bad"), total : 2 .

       

      I hope it's clear and that it can be done easily.

       

      Thanks.