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....
Solved! Go to Solution.
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?
|Customer||SLM 1||SLM 2||SLM 1 Sales||SLM 2 Sales||Total Sales|
That is an example of my transaction file, and this would be a table attached linking salesman numbers to salesman names
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] , ''))