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.

[sunny_talwar]

Do you have Region as one of the dimension where you are using this expression?

[jcampbell474]

No, Region isn't a dimension.  I only have two calculated dimensions.

=Agency_nbr&' ('&product&')'

=AgencyName&' ('&City&')'

[sunny_talwar]

I guess City can only belong to one and only one region?

[jcampbell474]

That is correct. 

[sunny_talwar]

Trying to understand why are you subtracting the same expression from the same 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,

The green and the red are the same expression... isn't it?

[jcampbell474]

Ah, yes, it is.  I didn't copy and paste it correctly.

The second (green) expression should have {$(=max(FiscalYearWeekBound)-1)} instead of {$(=max(FiscalYearWeekBound))}>}.

Here is the initial expression:

=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))

It's subtracting the previous week from the current week if the difference is >0.

[sunny_talwar · Accepted solution]

Let's try this

=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)))

[jcampbell474]

Thank you, Sunny.  It works perfect.  I didn't think of the Match approach.  Great idea!!

I'm sorry to bother you with this, but I thought that if I could get this one worked out I could change a value and it would work in another table.  This one is for 'Gainers' (sum > 0), compared to the previous week.

I also need 'Losers' (sum <=0), compared to the previous week.  I simply changed '>0' in your expression to '<=0' and it drops the match.  Dimensions and everything else is the same.  Any idea what could be happening?

=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]

Don't see anything wrong here syntax wise... what is the issue with this? Giving incorrect numbers?