topic Re: Count/aggr/sum if sales = 0 in QlikView

Count/aggr/sum if sales = 0

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

Hi All

I'm trying to make a calculation that counts if a product on a customer has been discounted 100%.

So basically below is what I'm trying. I have tried several different ways but none of the give me the correct number. Below is my latest attempt, and this one isn't working at all.

Count(If((aggr(Sum({< Promotion={'Not specified'}, BookingDate = {'>=15-05-2017'}>} Sales)=0, Customer_Name, SalesPerson.SALNAME,SalgsGr.SGRNAME, OrderItem_ID,Order_Number, Promotion))))

I have attached a sample.

I appreciate all the help I can get

Stefan

Re: Count/aggr/sum if sales = 0

marcus_malinow — Tue, 27 Jun 2017 07:59:21 GMT

Hi Stefan,

could you explain this in relation to the fields in your data model?

Based on your post above, are we looking here for a count of records where Sales = 0?

If this is the case, why not try adding a flag in to your load script? e.g.

if(Sales=0,1,0) as DiscountFlag

Marcus

Re: Count/aggr/sum if sales = 0

Anonymous — Tue, 27 Jun 2017 08:09:33 GMT

Hi Marcus

Yes I want to count whenever sales = 0, with above criteria taken into consideration.

Meaning I'll sum the sales based upon: Promotion={'Not specified'}, BookingDate = {'>=15-05-2017'}>} and several other criteria which are not included in this example.

I'll prefer not to put it into the script, I prefer an expression in this instance.

Re: Count/aggr/sum if sales = 0

swuehl — Tue, 27 Jun 2017 08:16:55 GMT

Maybe like

Sum(

Aggr(

If(Sum({<Promotion={'Not specified'}, BookingDate = {'>=15-05-2017'}>} Sales)=0,1,0),

Customer_Name, SalesPerson.SALNAME,SalgsGr.SGRNAME, OrderItem_ID,Order_Number, Promotion

)

Re: Count/aggr/sum if sales = 0

Anonymous — Wed, 28 Jun 2017 07:28:28 GMT

Hi Stefan

Thanks a lot.

It seems like it's working. However I'm sure I tried that solution before, and it didn't worked!

Stefan