How do I tally records conditionally ?

[#1]

Hi All,

I have a text box object which uses set analysis to display the number of sales reps which meet a certain condition. However, the tally isn't correct and I can't figure out why. This is the requirement.

I have an Excel file which holds many thousand records of sales transactions. The main fields for each record are Sales Rep, Region, Sales Amount and Sales Count (each record is set 'AS 1' during the load). From this data, I want to count (and display in a text box) the number of Sales Reps in the 'North' region who have accumulated sales totals of £150,000 or greater.

My set analysis looks like this:

sum({$<SalesRep ={"=sum(SalesAmount)>=150000"},Region={"North"}>SalesCount)

However, this statement is wrong as it returns the total number of sales records for those Sales Reps who have achieved sales of more than £150,000. I want it to return the total number of Sales Reps who have achieved sales of more than £150,000.

Can anyone help ? I'm afraid it's not possible to send the data or QV application.

MV

[simenkg]

count({$<SalesRep ={"=sum(SalesAmount)>=150000"},Region={"North"}>}distinct SalesRep)

[schlettig]

Hi Mareen,

if you have a unique id or else for your Sales Rep you could change the set analysis in:

count(distinct {$<SalesRep ={"=sum(SalesAmount)>=150000"},Region={"North"}> SalesRepID)

This would count all SalesRepID, that match the set and with the distinct they only get counted once.

Best regards,

Christian

[#4]

Hi,

Use something like this

=sum({<Amnt={'=sum(Amnt)>=15000'},Region={'NORTH'}>}SalesCount)

[#5]

Great Simen. Thank you very much.

[#6]

Thanks for your alternative solution Christian.

Much obliged

MV

[simenkg]

No problem.

If you consider the problem solved, then please mark the answer as correct.

Cheers.

[#8]

I can't find where you do this.