Sales Targets vs Actual by Month

[stuwannop]

Hi Everybody

I'm at my wits end here and can't work this out.

I have a list of salespeople in a table with monthly targets (which I created using a crosstab query) and I want to measure them against actual results in a sales order table.

The crosstab table works fine - it shows the sales target for each Month/Year (e.g. April 2012, Jun 2012 etc) for each individual salesperson.

My problem is I need to link both the Salesperson and the MonthYear target fields into the sales order table so I can see each salesperson, by month, their target vs actual, but of course when I do that I get Synthetic table (Salesperson and Date (monthyear) being the two linked fields). Hope that makes sense - thanks for any help.

[pover]

If you can I would concatenate the sales targets table with the sales order table so that they share the same salesperson, date and amount fields.  You could create 2 additional columns that identifies which rows are sales orders (eg. 1 as Sales_Order_Flag) and which rows are sales target (eg. 1 as Sales_Target_Flag).  Then in the report sum(Amount*Sales_Order_Flag) would give you the amount for Sales Order.

Otherwise, if you aren't interested in doing that much with the model, you can create a key that is like the following in both the sales order and sales target tables:

salesperson_ID & '_' & Date as Sales_Target_Key,

Comment out the salesperson and date field in the sales target table and that will get rid of you synthetic key.

Karl

[stuwannop]

Thanks Karl. Trying to concatenate the targets table - it is a Crosstab query though - do you know the syntax on where I put the concatenate command? Script is below:Directory;
CrossTable(MonthYear, TargetGP, 4)
LOAD ORDSalesperson,
ORDSalespersonFull,
JobTitle,
TargetFlag,
[Apr-11],
[May-11],
[Jun-11],
[Jul-11],
[Aug-11],
[Sep-11],
[Oct-11],
[Nov-11],
[Dec-11],
[Jan-12],
[Feb-12],
[Mar-12],
[Apr-12],
[May-12],
[Jun-12],
[Jul-12],
[Aug-12],
[Sep-12],
[Oct-12],
[Nov-12],
[Dec-12],
[Jan-13],
[Feb-13],
[Mar-13]
FROM
[Report Data\Targets.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);