Aggregate then Count with Condition - How to do this?

[#1]

Hi,

I have two input tables, as below:

Sales Table
Date	Region	SalesPerson	Sales
01/04/2014	R1	SP1	3
02/04/2014	R1	SP1	6
02/04/2014	R4	SP2	1
02/04/2014	R4	SP3	7
23/04/2014	R1	SP1	23
23/04/2014	R4	SP3	5
23/04/2014	R2	SP4	14
23/04/2014	R2	SP5	12
23/04/2014	R3	SP6	3
01/05/2014	R1	SP1	12
02/05/2014	R4	SP3	1
03/05/2014	R1	SP1	6
03/05/2014	R4	SP2	15
03/05/2014	R4	SP3	6
03/05/2014	R2	SP4	34
03/05/2014	R2	SP5	4
04/05/2014	R4	SP3	2
05/05/2014	R4	SP2	6
05/05/2014	R3	SP6	14
06/05/2014	R1	SP1	23
07/05/2014	R4	SP3	31
08/05/2014	R4	SP3	36

Target Table
Month	Region	Target
Apr-14	R1	2
Apr-14	R2	3
Apr-14	R3	2
Apr-14	R4	4
May-14	R1	2
May-14	R2	3
May-14	R3	3
May-14	R4	4

I would like to achieve a report table with Month-to-Date performance like this:

Month-to-Date
Region	SalesPerson Count (with >=20 sales in MTD total)	Target	% Achieve
R1	1	2	50%
R2	1	3	33%
R3	0	3	0%
R4	2	4	50%

What formula can I use for SalesPerson Count in order to achieve this?

Thanks in advance.

[SergeyMak]

Hi

It depends on your data model. But anyway you need additional Calendar table.

If you load everything into one table you just divide Sum({$<Type={"Actual"}>}Amount)/Sum({$<Type={"Budget"}>}Amount).

If you'd like to have these data in separate table, then you need to create linktable and connect your tables by synthetic key Region&Date AS %KEY

Then you just divide Sum(Sales)/Sum(Target)