Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
desauv86
Contributor

Count/aggr/sum if sales = 0

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count/aggr/sum if sales = 0

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

)

)

View solution in original post

4 Replies
marcus_malinow
Valued Contributor III

Re: Count/aggr/sum if sales = 0

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

desauv86
Contributor

Re: Count/aggr/sum if sales = 0

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.

MVP
MVP

Re: Count/aggr/sum if sales = 0

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

)

)

View solution in original post

desauv86
Contributor

Re: Count/aggr/sum if sales = 0

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