Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
Do you have Region as one of the dimension where you are using this expression?
No, Region isn't a dimension. I only have two calculated dimensions.
=Agency_nbr&' ('&product&')'
=AgencyName&' ('&City&')'
I guess City can only belong to one and only one region?
That is correct.
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?
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.
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)))
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)))
Don't see anything wrong here syntax wise... what is the issue with this? Giving incorrect numbers?