topic Re: Trying Analysis like for each in QlikView

Trying Analysis like for each

german_avanzato — Wed, 20 Nov 2013 13:26:21 GMT

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.

Re: Trying Analysis like for each

Nicole-Smith — Wed, 20 Nov 2013 20:57:04 GMT

See attached.

Re: Re: Trying Analysis like for each

german_avanzato — Thu, 21 Nov 2013 12:34:21 GMT

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

Re: Trying Analysis like for each

Nicole-Smith — Thu, 21 Nov 2013 13:18:20 GMT

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

german_avanzato — Thu, 21 Nov 2013 14:51:34 GMT

With this data:

Data:

LOAD * INLINE [

Year,Month,SalesMan,Client

2013,1,A,Cli-A

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

Re: Trying Analysis like for each

Nicole-Smith — Thu, 21 Nov 2013 15:48:28 GMT

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