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:
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 )|
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.
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,
With this data:
LOAD * INLINE [
It should be showing
|SalesMan||Total Visits||6 Visits||5 Vistis||4 Visits||3 Vistis||2 Visits||1 Visits|
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