Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an interesting scenario, hoping someone can give me some guidance...
I have a table of sales transaction. Each transaction can have a salesman1 and a salesman2. When I want to do an analysis of John Smith, I want the results to show all sales he was involved in whether he was labelled as salesman1 or salesman2 on the transaction. The salesman1/2 are ID's not names. Currently I have a seperate table linking all the ID's of salesman1 to a name.
In addition, the sales amount attributed to salesman1 and 2 differ on each transaction.
I hope this was clear....
This is not in the script it's just a table created in QV ... I've attache an example file you can have a look
To be more clear...
Every salesman has a number, on any transaction, that number can be associated in the SLM1 field or the SLM2 field.
When a transaction is completed, a percentage of the sales dollars is credited to SLM 1 and the rest to SLM2. This percentage can change on each transaction.
When I want to do an analysis of a specific salesman, I want qlikview to pull the sales dollar when the saleman is assocaited with SLM 1 and the sales dolalrs when the salesman is associated with SLM2. To get a total amount associated with the salesman.
If my statement were to be SUM(sales) SLM1 and SLM2 where SLM1 = SLM2 I would get zero as SLM1 will never = SLM2 on a given transaction.
Does anyone have any suggestions?
Would be easier if you could upload some data for the problem
Order | Customer | SLM 1 | SLM 2 | SLM 1 Sales | SLM 2 Sales | Total Sales |
1 | ABC | 1 | 6 | 90 | 10 | 100 |
2 | ABC | 2 | 150 | 150 | ||
3 | FE inc | 3 | 4 | 30 | 20 | 50 |
4 | Light | 4 | 6 | 25 | 10 | 35 |
5 | Light | 5 | 6 | 65 | 15 | 80 |
6 | Qink | 4 | 5 | 80 | 5 | 85 |
7 | Sutton | 3 | 10 | 10 | ||
8 | Sutton | 6 | 1 | 45 | 30 | 75 |
That is an example of my transaction file, and this would be a table attached linking salesman numbers to salesman names
Salesman | Salesman # |
John | 1 |
Pierre | 2 |
Bob | 3 |
Cara | 4 |
Lisa | 5 |
David | 6 |
Now when I do a search for "David" i want all the sales dollars to come up for him.
This is what my actual tables look like....
So in case of David the SLM 1 Sales = 45 and SLM 2 Sales = 35 right ....
You could create a table as under
Salesman as Dimension
Expression for SLM1Sales = sum ( if ( [Salesman #] = [SLM 1] , [SLM 1 Sales] , ''))
Expression for SLM2Sales = sum ( if ( [Salesman #] = [SLM 2] , [SLM 2 Sales] , ''))
Would this be in my script? How would I go about linking salesman names to the slm1 and slm2 numbers without creating a loop?
This is not in the script it's just a table created in QV ... I've attache an example file you can have a look
Thanks for all the help, I think I can make this work with my situation!