16 Replies Latest reply: Aug 3, 2017 3:10 PM by Sunny Talwar RSS

    General Expression Help

    Jason Campbell

      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.

        • Re: General Expression Help
          Sunny Talwar

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

            • Re: General Expression Help
              Jason Campbell

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

               

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

               

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

                • Re: General Expression Help
                  Sunny Talwar

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

                    • Re: General Expression Help
                      Jason Campbell

                      That is correct. 

                        • Re: General Expression Help
                          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?

                            • Re: General Expression Help
                              Jason Campbell

                              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.

                                • Re: General Expression Help
                                  Sunny Talwar

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

                                    • Re: General Expression Help
                                      Jason Campbell

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