General Expression Help

[jcampbell474]

I’m having a problem getting an expression to work.  It works in other objects where an ‘if’ statement isn’t used.  Can someone help me understand how I should implement it?

Context:

A change has been made that requires two products (HO and DF) be removed from the Southern and Western regions.  I use this in other objects and it works fine:

=if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt))

Here is the original expression (which works fine) that I need to change/update:

=if(sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt)>0,
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt))

What I’m trying to accomplish.  It says Expression OK and doesn’t identify any errors, but the object says Garbage after expression: “,”.

=if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) -
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) > 0,
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) -
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt))

Any and all help will be greatly appreciated.

[jcampbell474]

It includes products HO and DF in the results.  Seems like the Match condition isn't met.

I think I figured out what's going on.  Agencies can sell more than one product.  It doesn't happen in the Gainers table, only in the Losers.  Since Product is a dimension, should the expression contain aggregation?

[sunny_talwar]

Are you saying that you want to exclude any agency which sells HO or DF?

[jcampbell474]

Yes.  But, that same agency can sell PPA, CV, or MC.

product-={'HO','DF'}

[sunny_talwar]

So, do you want to put all Agencies into one bucket if one of there products is HO or DF? or do you not care about the Agency and look at the product on each row? If Agency ABC has PPA and HO, do they go in one bucket because one of the two products was HO or do they go in different buckets (HO is one and PPA in other)?

[jcampbell474]

Different buckets.  Agency+Product = Dimension (bucket)

[jcampbell474]

It was simple.  Just some errant parenthesis. Removed the blue and placed the red.

Thank you again for the help!

If(Match(Region,'Western','Southern'),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>} sold_cnt)),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt))))

[sunny_talwar]

Great