8 Replies Latest reply on Jun 10, 2013 9:30 AM by philmodjunk

    Pricing algorithm using Let function

    PeterMontague

      Title

      Pricing algorithm using Let function

      Post

            

           I've been trying to work out a pricing algorithm using Let functions. I'm having a couple of problems. 
             
      1.           I would like to reference the next seller instead of using seller 1, seller 2 etc. How do I achieve that in a calculation?
      2.      
      3.           I would also like to exclude certain sellers from the pricing comparison. I tried that with: 

           Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare";

           But it doesn't seem to be working at excluding these sellers when I use this calcualtion:
           Optimum price = If ( Seller 1 ≠ Excluded seller 1 ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 ) 
           These sellers are not being excluded.
           Here is the whole calcuation: Have you any advice?
            
           Let ( [ 
           Price 1 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 1 ) ; 
           Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ; 
           Price 2 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 2 ) ; 
           Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
           Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;
           Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare"; 
           Amazon shipping = "2.80" ;
           Optimum price = If ( Seller 1 ≠ Excluded seller 1 ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 ) 
           ] ; 
           If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
           )

        • 1. Re: Pricing algorithm using Let function
          philmodjunk

               Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare";

               is a boolean expression. It will return either True (1) or False (0). Thus,  Seller 1 ≠ Excluded seller 1 will almost certainly always evaluate as True.

               Try this expression in place of Excluded seller 1:

               Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );

               and replace Seller 1 ≠ Excluded seller 1 with:

               IsEmpty ( FilterValues ( Excluded Seller list ; Seller 1 ) )

          • 2. Re: Pricing algorithm using Let function
            PeterMontague

                 Thanks Phil. I made those changes. My calculation looks like this

                  

                 Let ( [ 
                 Price 1 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 1 ) ; 
                 Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ; 
                 Price 2 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 2 ) ; 
                 Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
                 Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;
                 Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );
                 Amazon shipping = "2.80" ;
                 Optimum price = If ( IsEmpty ( FilterValues ( Excluded Seller list ; Seller 1 ) ) ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 ) 
                 ] ; 
                 If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
                 )
                 But it is still making a comparison to the excluded sellers' prices.
            • 3. Re: Pricing algorithm using Let function
              philmodjunk

                   Have you check to be sure that each caclulation variable, (price 1, shipping 1, etc...) is being correctly parsed? Have you checked to be sure that the values in the excluded list exactly match the values being parsed for seller 1 when the value for seller 1 is an excluded seller?

              • 4. Re: Pricing algorithm using Let function
                PeterMontague

                     I switched your filter around. I also checked over my calculation and found that seller 2 was coming up with the wrong calculation. It was returning the shipping cost for seller 1 instead of seller 2's price.

                If ( IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller list ) )

                So that has sorted me out to a certain extent. Sometimes I need to exclude seller 2 and seller 3 if they are on my excluded seller list.

                I would like to reference the next seller instead of using seller 1, seller 2 etc. How do I achieve that in a calculation? Do I need to set a global variable? If so how?

                • 5. Re: Pricing algorithm using Let function
                  philmodjunk

                       It depends on how your calculation is set up. In your example so far, those calculation variables, seller 2 and seller 3 are not part of the calculation.

                       You might even need a loop--either with a script or a recursive custom function to loop through the values until you reach the one that you don't want to omit.

                       Another option might be to extract all these values into related records and then use a relationship or scripted find to either omit or delete the records for values that you don't want.

                  • 6. Re: Pricing algorithm using Let function
                    PeterMontague

                         I suppose the second option - using related records is the one that I would be most familiar with. But if it was simpler to make a recursive custom function I wouldn't mind trying that.

                    • 7. Re: Pricing algorithm using Let function
                      PeterMontague

                           I've tried making my own loop within my calculation. But I don't think it is working properly.

                            

                           Let ( [ 
                           Price 1 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ; 
                           Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ; 
                           Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;
                            
                           Price 2 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ; 
                           Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ; 
                           Seller 2 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 2 ) ;
                            
                            
                           Price 3 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 3 ) ; 
                           Shipping 3 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 3 ) ; 
                           Seller 3 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 3 ) ;
                            
                           Price 4 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 4 ) ; 
                           Shipping 4 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 4 ) ;
                           Seller 4 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 4 ) ;
                            
                           Price 5 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 5 ) ; 
                           Shipping 5 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 5 ) ;
                           Seller 5 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 5 ) ;
                            
                           Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );
                           Amazon shipping = "2.80" ;
                            
                           Option 1 =  If (IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller List ) ) ; (Price 1 + Shipping 1  - Amazon shipping) * .99) ;
                           Option 2 = If ( IsEmpty ( FilterValues ( Seller 2 ; Excluded Seller List ) ) ; (Price 2 + Shipping 2  - Amazon shipping) * .99 ; (Price 3 + Shipping 3 - Amazon shipping) * .99 );
                           Option 3 = If ( IsEmpty ( FilterValues ( Seller 3 ; Excluded Seller List ) ) ; (Price 3 + Shipping 3  - Amazon shipping) * .99 ; (Price 4 + Shipping 4 - Amazon shipping) * .99 );
                           Option 4 = If ( IsEmpty ( FilterValues ( Seller 4 ; Excluded Seller List ) ) ; (Price 4 + Shipping 4  - Amazon shipping) * .99 ; (Price 5 + Shipping 5 - Amazon shipping) * .99 );
                            
                           Optimum price = If (IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller List ) ) ; If ( IsEmpty ( FilterValues ( Seller 2 ; Excluded Seller List ) ) ; (Price 2 + Shipping 2  - Amazon shipping) * .99 ; If ( IsEmpty ( FilterValues ( Seller 3 ; Excluded Seller List ) ) ; (Price 3 + Shipping 3  - Amazon shipping) * .99 ; If ( IsEmpty ( FilterValues ( Seller 4 ; Excluded Seller List ) ) ; (Price 4 + Shipping 4  - Amazon shipping) * .99 ; (Price 5 + Shipping 5 - Amazon shipping) * .99 ) ) ))
                           ] ; 
                           If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
                           )
                           The last part is a nested function where the defalut value is option 2, 3, up to option 4. I don't think there will be a fifth unwanted book seller. I have tested this formula and it is really hard to see which part is not working. But I can clearly see that it goes as far as the second price and doesn't go onto the third price if it needs to.
                           Would 
                           Case (option 1: option 2; option 2; option 3; option 3; option 4)
                           work better? Its much easier to read. But I don't think it would work either.
                      • 8. Re: Pricing algorithm using Let function
                        philmodjunk

                             The "loop" would be either in a script or custom function.