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:

Year	Month	SalesMan	Client
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

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 )
2	0	0	1	0	1	0
3	0	0	0	0	1	2

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.

[Nicole-Smith · Accepted solution]

See attached.

[german_avanzato]

Hi Nicole, thanks for your answer.

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!!

[Nicole-Smith]

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

[german_avanzato]

With this data:

Data:
LOAD * INLINE [
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

SalesMan	Total Visits	6 Visits	5 Vistis	4 Visits	3 Vistis	2 Visits	1 Visits
A	3			1	1
B	2					1	2

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

[Nicole-Smith]

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