topic Dividing by a count ignoring a dimension expression in QlikView

Dividing by a count ignoring a dimension expression

bazzaonline — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have a stacked chart with has two dimensions.

Order Month and then a sum of the spend to group by bands

= Aggr( if(Sum(spend)<3000,dual('0-3k',1), if(Sum(spend)<=10000,dual('3k-10k',2), if(Sum(spend)<=100000,dual('10k-100k',3), if(Sum(spend)<=300000,dual('100k-300k',4), dual('300k+',5))))),OrderNumber)

I then have an expression sum(spend) which gives me a stacked chart showing the amount of spend by the amount of the order. I want to be able to divide this by the total count of OrderNumber.

Any ideas ie if I had three orders, two at 20 and one at 10. It would do a bar 20 / 3 and then stacked on top 10 / 3??

Re: Dividing by a count ignoring a dimension expression

sunny_talwar — Wed, 11 Mar 2020 14:34:25 GMT

May be just this

Count(TOTAL OrderNumber) or Count(DISTINCT TOTAL OrderNumber)

Re: Dividing by a count ignoring a dimension expression

bazzaonline — Wed, 11 Mar 2020 14:37:57 GMT

I tried sum(spend)/count(Distinct Total ordernumber) and the result is much to low?

Re: Dividing by a count ignoring a dimension expression

sunny_talwar — Wed, 11 Mar 2020 15:30:22 GMT

How about sum(spend)/count(Total ordernumber)