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

# Trying Analysis like for each

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.

• ###### Re: Trying Analysis like for each

See attached.

• ###### Re: Re: Trying Analysis like for each

In your example, the code works because you only have a sale per month (that not how it´s in my model).

I attached the example with more data, and you will see that isnt working.

However, you give me a clue to solve this, I will try to create a table with the information aggregated, and count over that table instead of the fact table.

I will post the result,

Thanks again!!

• ###### Re: Trying Analysis like for each

The example that you attached with more data--can you elaborate as to what it should be showing?

• ###### Re: Re: Trying Analysis like for each

With this data:

```Data:
Year,Month,SalesMan,Client
2013,1,A,Cli-A
2013,1,A,Cli-A
2013,1,A,Cli-B
2013,1,A,Cli-B
2013,1,A,Cli-A
2013,2,A,Cli-A
2013,3,A,Cli-A
2013,4,A,Cli-A
2013,5,A,Cli-B
2013,6,A,Cli-B
2013,1,B,Cli-B
2013,2,B,Cli-A
2013,3,B,Cli-C
2013,4,B,Cli-C
];
```

It should be showing

SalesManTotal Visits6 Visits5 Vistis4 Visits 3 Vistis2 Visits1 Visits
A311
B212

With the SalesMan A, he visits

Client : Cli-A 4 times in the past 6 month (count 1) for column: 4 visits (its only count 1 visits per year / month, It doesnt matter if it has more than 1 visit in the same year / month)

Client : Cli-B  3 times in the past 6 month (count 1) for column : 3 visits

The problem is that in the aplication, is showing 1 for the column 6 visits and 1 for the column 4 visits.

It´s because it counts all the clients for the month 1

Meanwhile, I solved this, creating a table grouping by this fields, and using the syntax that is in the example, and its working

• ###### Re: Trying Analysis like for each

I'm glad you were able to get it to work