topic Re: How do I tally records conditionally ? in QlikView

How do I tally records conditionally ?

Tue, 06 Jan 2015 13:24:23 GMT

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.

Re: How do I tally records conditionally ?

simenkg — Tue, 06 Jan 2015 13:30:04 GMT

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

Re: How do I tally records conditionally ?

schlettig — Tue, 06 Jan 2015 13:33:27 GMT

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

Re: How do I tally records conditionally ?

Tue, 06 Jan 2015 13:39:21 GMT

Hi,

Use something like this

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

Re: How do I tally records conditionally ?

Tue, 06 Jan 2015 14:01:26 GMT

Great Simen. Thank you very much.

Re: How do I tally records conditionally ?

Tue, 06 Jan 2015 14:02:20 GMT

Thanks for your alternative solution Christian.

Much obliged

Re: How do I tally records conditionally ?

simenkg — Wed, 07 Jan 2015 10:07:18 GMT

No problem.

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

Cheers.

Re: How do I tally records conditionally ?

Wed, 07 Jan 2015 13:23:43 GMT

I can't find where you do this.