Count/aggr/sum if sales = 0

[Anonymous]

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

[marcus_malinow]

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

[Anonymous]

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.

[swuehl · Accepted solution]

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

)

)

[Anonymous]

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