7 Replies Latest reply: Nov 10, 2011 4:21 PM by Deepak Vadithala RSS

    Set Analysis do not match my previous IF expression?

    Robert Svebeck

      Hi all

      I cloned a table chart with and expression based on "IF" and converted the expression to Set Analysis instead.

      Original expression:

      Sum(
      {1<myDate={$(vMyPeriod)},
      myGroup < {96},
      myCat < {90},
      Salestype = {"External"},
      CashType = {"Cash"}
      >} Qty)


      Replaced with this set expression:

      Sum(IF
      (
      myDate = vMyPeriod
      AND myGroup < 96
      AND myCat < 90
      AND Salestype = 'External'
      AND CashType = 'Cash'
      ,Qty))


      But when I compare the numbers of the two tables, the numbers are "slighly" different.

      Is my SET expression wrong or do I have corrupt data?

      I have made NO selections in my document.

      Best Regards,

      Robert Svebeck

        • Set Analysis do not match my previous IF expression?
          Stefan Wühl

          Robert, I assume you mixed up the original and replaced expressions, right?

           

          So, as set expression, I would suggest something like:

          Sum(
          {1<myDate={$(vMyPeriod)},
          myGroup = {"<96"},
          myCat = {"<90"},
          Salestype = {'External'},
          CashType = {'Cash'}
          >} Qty)

           

          Note how I changed the set modifiers to search terms for myGroup and myCat. This set expression should be even constant with regard to changed selections, except for the vMyPeriod maybe (because you are using set identifier 1), right?

           

          Hope this helps,

          Stefan

            • Re: Set Analysis do not match my previous IF expression?
              John Witherspoon

              In addition to what Stefan said, an if() statement respects existing selections, while a {1} set and an = in set analysis does not.  You're overriding everything instead of behaving like an if().  That'll work when you make no selections, but then return different results when you DO make selections.  I assume you want to match the if() in all cases.  So get rid of the {1} set, and use *= for your field checks to intersect with current selections instead of overriding.

               

              sum({<myDate*={$(vMyPeriod)}

                   ,myGroup*={"<96"}

                   ,myCat*={"<90"}

                   ,Salestype*={'External'}

                   ,CashType*={'Cash'}

                   >} Qty)

               

              This expression will NOT be constant when you change selections, but it isn't intended to be.  It's intended to match the if().  Hopefully I got it right.

                • Re: Set Analysis do not match my previous IF expression?
                  Robert Svebeck

                  Hi guys,

                  Thanks for your help! I know have the same numbers in both tables.

                  I am aware that the SET and IF are different when it comes to making selections. That is the reason why I desided to change method from IF to SET, since I wanted the calculation to be selection independent.

                  That is also why I was confused that I got different result even though I made sure I had no selection made before comparing the two tables.

                  The solution was to put < inside brackets:

                  from my code: myCat < {90}, to your code: myCat = {"<90"}

                  But I still do not really understand the difference between these two methods. What was beeing calculated with my code, since the result was almost only 1% difference....?

                  John is suggesting to put *= after all fields, when I try that - there is no difference in the result,  it is equally aqurate to what Stefan suggested - so John: why use the *= instead of just = ? Is there a difference between those two? (myCat = {"<90"} and myCat *= {"<90"})

                   

                   

                  Regards, Robert

                    • Set Analysis do not match my previous IF expression?
                      Stefan Wühl

                      Robert, you just can't use a comparison operator like < as field selection assignment operator in your set modifier, you need an equal sign here (kind of logical, it's kind of value assignment to a field name, like a selection, isn't it?).

                      You may use an implicite set operator like *=, +=, -=, /= though, this is just a short form, e.g.

                      <Year += {2007, 2008}> is equivalent to <Year = Year +  {2007, 2008}>

                       

                      So, if you need to retrieve your field values by comparison to a value instead of explicitely stating same as element list, you need a search expression. The search expression needs to be enclosed in double quotes, so in total you get something like <myCat = {"<90"}> as your set modifier.

                       

                      Hope this clarified your first question. I' not sure, but I think if you write it like <myCat < {90}> this invalids the complete or part of your set expression, so I think you get a result equal to removing  the set modifiers from your expression. Depending on your selection, this might be close to your expected result, but could be also miles off.

                       

                      And coming to your second question, I believe there should be a difference - if you do make a selection and your selection is different from the one defined in your set expression.

                       

                      Not sure why you don't see a difference. Could you post your current complete expression? Or a small sample app that shows the issue?

                       

                      Regards,

                      Stefan

                      • Re: Set Analysis do not match my previous IF expression?
                        John Witherspoon

                        Robert Svebeck wrote:

                         

                        Is there a difference between those two? (myCat = {"<90"} and myCat *= {"<90"})

                         

                        Yes, as I was trying to say, = is an override, while *= is intersection.  So let's take an example.  Let's say you have a field Product with values A, B, C and D.  Let's say you select A and B. 

                         

                        {<Product={'B','C'}>} returns B and C, because it overrides your selections.
                        {<Product*={'B','C'}>} returns only B, because it intersects your selections.

                         

                        An if() behaves like *=.

                         

                        However, it sounds like you do want to override, so stick with what Stefan said.