topic Re: Count in expression in QlikView

Count in expression

Tue, 14 Jan 2014 20:34:38 GMT

I have an expression that works well at giving me the weekly average of a sku for the first 13 weeks.

(Sum( if(MonthlyTotal<=3,SalesAmount))/13)/count(DISTINCT ItemNumberCommon)

I want to count how many skus that are above X dollar amount, so for example how many sku are above $250 per week. How can I do this?asd

Lance H. James, CPA

Director of Finance

phaneendra_kunc — Tue, 14 Jan 2014 20:45:35 GMT

quick question: SKU is a dimension in your model ?

and you are looking for Count of SKU's where sum(SalesAmount) is > 250 right?

Can you post a sample...?

Tue, 14 Jan 2014 20:49:38 GMT

This expression gives me a weekly sales amount.

(Sum( if(MonthlyTotal<=3,SalesAmount))/13)/count(DISTINCT ItemNumberCommon)

I want to count the when the above expression is above $500.

Lance H. James, CPA

Director of Finance

The Aspen Brands Company

Kate Aspen • Baby Aspen

www.kateaspen.com<http://www.kateaspen.com> • www.babyaspen.com<http://www.babyaspen.com>

T: 678.282.0053 ext. 777

2400 Chattahoochee Drive

Duluth, GA 30097

2013, 2012 & 2011 Top Places to Work - Atlanta Journal Constitution

Tue, 14 Jan 2014 21:05:16 GMT

Hey Lance,

Ok so lets call this expression you are using as 'Exp'.

You would be using this expression in a table with some dimensions.

So if you want to count and put the result in a text box you could use something like

=count(if(aggr(Exp,Dim1,Dim2)>=30,Dim3))

I have attached a sample file which might help you. I used a simple sum but it should work for your case as well.

Thanks