Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)
)
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
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.
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
)
)
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