Set analysis: inserting a field value into an expression

[#1]

Hello

I hope I explain this clearly. It's driving me mad, but I'm sure there is a simple solution.

You have a sales transactions table, with two columns: Product and SaleAmount. Let's say you have 5 products (Prod1, Prod2, ..., Prod5) and about 100,000 rows of data in this table, one for each transaction in a product.

e.g.

Product  SaleAmount

-----------------------------

Prod1, 100

Prod2, 1000

Prod1, 500

Prod5, 400

...etc...

I then have a second table, with Salesperson and ProductsToTrack

e.g.

Salesperson   ProductsToTrack
-----------------------------
Salesperson1, Prod1
Salesperson2, Prod2,Prod3,Prod5
Salesperson3, Prod4,Prod5
Salesperson4, Prod5
Salesperson5, Prod1,Prod2

Because this is a simplified version of my actual app, I just want to point out that it is important that I add the products to the second table in a delimited list format.

All I want to do is build a chart, with the first two columns from the second table and the expression equal to the sum of the SaleValue from the sales transaction table. So the chart dimensions are:

Salesperson
ProductsToTrack

The expressions, when properly evaluated, would look something like this (shown for what each row should calculate) - i.e. this is what I want to happen:

=sum( {< Product = {Prod1} >}             SaleValue )
=sum( {< Product = {Prod2,Prod3,Prod5} >} SaleValue )
=sum( {< Product = {Prod4,Prod5} >}       SaleValue )
=sum( {< Product = {Prod5} >}             SaleValue )
=sum( {< Product = {Prod1,Prod2} >}       SaleValue )

The thing I can't work out is: how do I get the ProductsToTrack inside the set analysis part of the expression? There's only one record for each Salesperson, and so therefore only one ProductsToTrack list per person.

I've tried, amongst other things:

=sum( {< Product = ProductsToTrack >} SaleValue )
=sum( {< Product = {$=(ProductsToTrack)} >} SaleValue )
=sum( {< Product = {$=(Aggr(ProductsToTrack, SalesPerson)} >} SaleValue )

None of these work.

Help!?! What am I missing?

Many thanks
James

[Miguel_Angel_Baeyens]

Hi James,

If the relationship is 1:1 you usually do not require specifying the field in the set modifier, as a general rule. I'd rather create in the script what is known as a "link table" between the product table and the salesperson table, so the Sum() function is easier and returns the values as expected, by using SubField() or similar, in this case.

Hope that helps.

Miguel

[#3]

Thank you very much for taking the time to reply, and for showing the results in an app.

I understand exactly how the link table works, but I discounted this as a solution since my app is more complex than that described above (which is why I mentioned about keeping the delimited list). I tried to simplify the problem to exactly what I thought I needed, and I'm not convinced I can use a link table.

Let me give you a bit more detail and hopefully you can prove me wrong.

My actual app has another table in the chain.

Table 1: I have a table of let's call them "sales campaigns." The ProductsToTrack are defined at this level.

Table 2: Each sales campaign is made up of different sales people. I have perhaps 100 campaigns, each made up of between 50 and 500 sales people.

Table 3:  Finally, the sales people link to the transactions table, which is where the products are stored.

So there is no direct link between the two tables that reference products: there is another table in the middle.

I have re-attached your file, with the actual data model.

Thanks so much again

James

[Miguel_Angel_Baeyens]

Hi James,

Sticking to my first idea, the link table, would this model work with your data?

Regards,

Miguel

[#5]

Hi Miguel

Thank you for your reply again. I'm afraid this doesn't work.

There are now two salesperson fields. They need to be linked. For example, if I pick a salesperson in the transactions table (salesperson2 in your example), I'd want to see to which campaigns they are attached.

Also, I've created a chart based on this data model and the totals aren't correct (each campaign is summing up the total inflows for each product in each campaign, and not just the sales people defined in that campaign.

Coming back to my original idea, is there no way to take a field value (1:1) and put it in an expression as a value?

Thanks again

James