Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Aggregate then Count with Condition - How to do this?

Hi,

I have two input tables, as below:

Sales Table
DateRegionSalesPersonSales
01/04/2014R1SP13
02/04/2014R1SP16
02/04/2014R4SP21
02/04/2014R4SP37
23/04/2014R1SP123
23/04/2014R4SP35
23/04/2014R2SP414
23/04/2014R2SP512
23/04/2014R3SP63
01/05/2014R1SP112
02/05/2014R4SP31
03/05/2014R1SP16
03/05/2014R4SP215
03/05/2014R4SP36
03/05/2014R2SP434
03/05/2014R2SP54
04/05/2014R4SP32
05/05/2014R4SP26
05/05/2014R3SP614
06/05/2014R1SP123
07/05/2014R4SP331
08/05/2014R4SP336

Target Table
MonthRegionTarget
Apr-14R12
Apr-14R23
Apr-14R32
Apr-14R44
May-14R12
May-14R23
May-14R33
May-14R44

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

Month-to-Date
RegionSalesPerson Count (with >=20 sales in MTD total)Target% Achieve
R11250%
R21333%
R3030%
R42450%

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

Thanks in advance.

1 Reply
Partner
Partner

Re: Aggregate then Count with Condition - How to do this?

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)

Regards,
Sergey