1 2 Previous Next 15 Replies Latest reply on Jul 12, 2017 10:01 AM by Jay Morehart

    Calculated field for selection

    qamar zaman

      Hi All,

       

      My current view is like this:

       

      I dont want to show this highlighted row as per this criteria.

      If shop/competitor/cat1/brand/size are same (and price of ToyTop is lower than price of Competitor) then show Itemcode where APD is min.

      twbx file is also attached.

       

      Any help?

       

      thanks

        • 1. Re: Calculated field for selection
          Joe Oppelt

          (V 10.0 here)

           

          See attached.  I didn't understand what you meant by "APD is min", but you can take what I did and modify it as you need.

           

          In the attached I created a calc [index] that just figures out how many rows are in a given combination of shop/competitor, etc.  Notice that I did a special setting of how to address this table calc.  (Edit Table Calc for [index] on the measure shelf.)

           

          You can use that in a subsequent calc to know whether you need to evaluate that row.  See [Do I want to display this?] calc.  If it's a second row, then you can check the values of the other fields and display it if warranted.  Notice that I set a 1 or 0.  You can filter for values = 1.

          • 2. Re: Calculated field for selection
            qamar zaman

            Hi Joe Oppelt

             

            APD is a calculated filed for average price difference

            • 3. Re: Calculated field for selection
              Joe Oppelt

              I see that.

               

              What I didn't understand is your requirement that it is min.  MIN of what?

              • 4. Re: Calculated field for selection
                Jay Morehart

                If I understand your goal correctly, you can do this with an LOD

                if by min APD you mean "closest to 0" then the LOD would look like this:

                 

                [Keep?] =

                [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                 

                resulting in:

                we can add in your requirement of the ToyTop price being lower (APD is negative):

                [Keep?] =

                [APD] <0 and

                [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                 

                giving:

                 

                Is this what you are looking for?

                • 5. Re: Calculated field for selection
                  qamar zaman

                  Hi Jay Morehart

                   

                  Thanks for your reply. what I want is little different that your final output. If I select Keep?=False, two rows will be removed. But I need both in final output (see attachment for what i need in and out should be out)

                   

                  Selection.PNG

                   

                   

                  I will explain my requirement again.

                  If shop/competitor/cat1/brand/size are same for two Itemcodes and APD is negative for both then keep itemcode where APD is close to zero.

                  and

                  If shop/competitor/cat1/brand/size are same for two Itemcodes and APD is negative for one and positive for other then keep both itemcodes.

                  and

                  If shop/competitor/cat1/brand/size are same for two Itemcodes and APD is positive for both then keep both itemcodes.

                  • 6. Re: Calculated field for selection
                    qamar zaman

                    Hi Joe Oppelt

                     

                    Your final output is nearly the same what i want,  but in one case we have

                    shop/competitor/cat1/brand/size are same for two Itemcodes, and APD is negative for one and positive for other, in that case i need both itemcodes in final report.

                     

                    I have attached screenshot of your proposed solution, If i select (Do I want to show)=0 then two rows will be omited. But only one should be eleminated with my requirements(third one)

                     

                    Selection.PNG

                     

                    thanks

                    Qamar

                    • 7. Re: Calculated field for selection
                      Jay Morehart

                      you just need to add some extra logic to my calc:

                       

                      for clarity the logic is: (this works in 10.3, IF can now return a Boolean!):

                      if

                          {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: min([APD])} < 0 and

                          {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Max([APD])} < 0 //all APD for this combo are negative

                      then [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                      else TRUE

                      END

                       

                      For previous versions, we have to convert this to a pure Boolean statement:

                      {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: min([APD])} < 0 and

                          {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Max([APD])} < 0 //all APD for this combo are negative

                          and [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                      OR

                      NOT (    {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: min([APD])} < 0 and

                          {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Max([APD])} < 0 )

                       

                      hope this helps!

                      • 8. Re: Calculated field for selection
                        Joe Oppelt

                        Qamar -- In your screen shot, for 128693 (the one you did NOT want to hide), if the Avg Sale Price for that one had been MORE than the competitor, would you still have wanted to hide 267973?

                         

                        I'm beginning to understand your requirements better.  If there are more than one row for a given combination of all the other dimensions, then maybe we want to hide the extras.

                         

                        Could there be more than 2 rows to evaluate?  If were going to eliminate one, will it always be the last row?  If there are 3 or more, will we only eliminate one?

                        • 9. Re: Calculated field for selection
                          Joe Oppelt

                          Jay -- Qamar is on 10.0, so let's not use 10.3 toys here.


                          Also, I think the criterion for MIN is not so much that it is a negative value, but that it's the minimum of the bunch.  And I'm not sure if the difference between the Actual and Competitor prices impact which APDs we look at.

                          • 10. Re: Calculated field for selection
                            Jay Morehart

                            Joe,

                            I included the 10.3 logic because I felt that it was more readable, sometimes pure Boolean can be hard to wrap your head around, I would have included psudocode (or extensive comments) if 10.3 didn't handle it that way.

                             

                            if both the min and the max APD for a given set of shop/competitor/cat1/brand/size are negative, then ALL [APD] for that set are negative (whether it is 2 or 10) which satisfies the first part of conditional 1: "and APD is negative for both"  and then we grab the one closest to 0: [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                            the rest of the conditionals keep both (and I assumed "all" if there were more than 2)

                             

                            And I'm not sure if the difference between the Actual and Competitor prices impact which APDs we look at I am not sure I understand this statement, the APD based on the ratio of the Actual and Competitor price.

                            shop/competitor/cat1/brand/size

                            • 11. Re: Calculated field for selection
                              qamar zaman

                              Hi Joe Oppelt

                               

                              In this below attachement i have 5 different cases, in the last coulmn i wrote which one i want to keep in analysis and which one not. I hope it is clear now.

                               

                              Shop= xx (TopToy), column X and y could be some dimenssions.

                               

                              Case 1. If we have two itemcodes where all other dimensions are same and toptoy is expensive in both itemcodes, then keep both.

                              Case 2. If we have two itemcodes where all other dimensions are same and toptoy is expensive in one itemcode and cheaper for other, then keep both.

                              Case 3. If we have two itemcodes where all other dimensions are same and toptoy is Cheaper in both itemcodes, then keep itemcode where APD% is closer to Zero.

                              Case 4. If we have 4 itemcodes where all other dimensions are same and toptoy is Cheaper for one itemcode and expensive for other three, then keep all itemcodes.

                              Case 5. If we have 4 itemcodes where all other dimensions are same and toptoy is Cheaper for two itemcodes and expensive for two others, then keep both itemcodes where toptos is expansive and also keep one itemcode where toptoy is cheaper but APD% is closer to zeo .

                               

                              Selection.PNG

                               

                              Thanks

                              Qamar

                              • 12. Re: Calculated field for selection
                                qamar zaman

                                hi Jay Morehart

                                 

                                Thanks for the reply, I have applied your solution to my sheet. but it does not work if we have more than 2 itemcodes where all other dimenssions are same ( see above case 4 and case 5)

                                Thanks again Jay Morehart and Joe Oppelt for your efforts.

                                 

                                Looking forward

                                Qamar

                                • 13. Re: Calculated field for selection
                                  Jay Morehart

                                  If you have cases that don't fall in to the current logic, yes they will break. the method, however, will work; you just need to add the logic to handle the additional cases. again it might be easier to write it out in If-Then format and then convert it to boolean, alternatively you can leave it in If-Then format and return strings "Keep" and "Ignore" instead of boolean TRUE or FALSE, this will keep the readability and function in 10.0

                                  something like:

                                  if

                                      {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: min([APD])} < 0 and

                                      {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Max([APD])} < 0 //all APD for this combo are negative

                                  then

                                    if [APD] = {FIXED [Shop],[competitor ID],[Cat1],[brand],[size]: Min(abs([APD])) * Max(if [APD]>=0 then 1 else -1 end)}

                                    then "Keep"

                                    else "Ignore"

                                    END

                                  //elseif <condition 5>

                                  //elseif <condition x>

                                  else "Keep" //if not all negative, keep all

                                  END

                                   

                                  remember that this processes from top to bottom so if there are conditions that overlap you may need to change the order of the "ifs". in general the more complex condition will go first (so condition 5 may need to be your first "IF")

                                  if I am looking at the correctly condition 5 is the only one not handled my my calculation in the current form. you can write an if for each condition, but it is often possible to simplify it by noticing similarities between conditions. For example Condition 1,2 and 4 all keep all codes:

                                  IF "TopToy more expensive in all" or "Less expensive in only 1 code" THEN keep ALL

                                  this logic covers those 3 cases (and in my original calc is caught in the "else" keep), so you may want to add this explicit logic instead of catching it in the else ( I often try to get all the conditions explicitly and do an else "Unknown" so I can find the uncaught conditions)

                                   

                                  Again if you only have the 5 conditions above, you should be good just adding the logic for the 5th condition, if there are others as well, you may want to tweak the IF structure slightly to explicitly handle 1,2, and 4.

                                   

                                  Hope this helps!

                                  • 14. Re: Calculated field for selection
                                    qamar zaman

                                    Hi Jay Morehart

                                     

                                    I tried to cover other conditions with keep2?, BUT it is not working . could you please have a look, workbook is attached.

                                     

                                    Keep2?=

                                    IF

                                    { FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile],[Size]:MIN([APD])}<0

                                    and

                                    { FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile],[Size]:MAX([APD])}>0

                                     

                                    THEN

                                    IF (([APD]<0 AND [APD]={ FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile],[Size]:MIN(ABS([APD]))*MAX(if [APD]>=0 then 1 ELSE -1 END)})  OR [APD]>0)

                                        then "Keep"

                                        ELSE "Ignore"

                                        END

                                     

                                    ELSEIF

                                    { FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile],[Size]:MIN([APD])}<0

                                    and

                                    { FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile], [Size]:MAX([APD])}<0

                                     

                                    THEN

                                        IF [APD]={ FIXED [Shop1],[Competitor Website],[competitor ID1],[Dt Sowi],[Brand],[Profile],[Size]:MIN(ABS([APD]))*MAX(if [APD]>=0 then 1 ELSE -1 END)}

                                        then "Keep"

                                        ELSE "Ignore"

                                        END

                                     

                                    ELSE "Keep"

                                    END

                                    1 2 Previous Next