4 Replies Latest reply on Jan 29, 2019 7:33 AM by Ivona Karteva

    use several IF functions in one calculated field + using LOD function

    Ivona Karteva

      Hey guys,

       

      I know how  i can reach the target i want by creating two calculated fields with two functions however i want to combine them in one.

      Below are two functions which work when making them separately but not together in one calculated field

       

      IF [size1 (shelf_stock)]= 0 THEN{ INCLUDE

       

       

      [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

       

       

      MAX([quantity (shelf_stock)])*AVG([average_price]) }

       

      2.

      IF [size1 (shelf_stock)] is different than 0 THEN

       

      { INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

       

       

      MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END

       

      Does this make sense?

        • 1. Re: use several IF functions in one calculated field + using LOD function
          Jim Dehner

          well the syntax is not correct

          this is closer

           

          IF [size1 (shelf_stock)]= 0 THEN{ INCLUDE

           

           

          [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

           

           

          MAX([quantity (shelf_stock)])*AVG([average_price]) }

           

          else

           

          { INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

           

           

          MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END

           

           

          but this is very inefficient in processing - comput the LOD's out side the conditional then just place the value in the conditional - computing inside the conditional is done one at a time versus doing it all at once when calculated outside the conditional

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: use several IF functions in one calculated field + using LOD function
            Deepak Rai

            I am using INCLUDE as you Used, But you can use FIXED also:

             

            { INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:MAX(IF [size1 (shelf_stock)]= 0 THEN ([quantity (shelf_stock)])*AVG([average_price])ELSE ([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) END}

            • 3. Re: use several IF functions in one calculated field + using LOD function
              Diego Parker

              Hey Ivona,

               

              If the two calculations are working, then I believe the following should work:

               

              IF ([size1 (shelf_stock)]= 0 THEN{ INCLUDE

               

               

              [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

               

               

              MAX([quantity (shelf_stock)])*AVG([average_price]) } END)

               

              ELSEIF ([size1 (shelf_stock)] <> 0 THEN

               

              { INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

               

               

              MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END)

               

              END   

               

              Hope that works. If does please mark it as helpful and correct so other users can refer to it.

               

              Best,

              Diego

              • 4. Re: use several IF functions in one calculated field + using LOD function
                Ivona Karteva

                This just popped out in my mind but somehow it doesn’t work.

                I will have to check the database and I might be doing something wrong choosing the wrong data fields.

                 

                 

                 

                 

                Ivona Karteva

                Supply Chain Manager

                email: ivona.karteva@teolino.eu

                phone: +35932904905

                mobile: 0886707257

                Teolino Plast LTD

                Plovdiv, Bulgaria

                55 Nestor Abadjiev

                www.teolino.eu

                This e-mail message and its attachments are intended solely for the use of the addressee and may contain legally privileged and confidential information. If the reader of this message is not the intended recipient, nor an employee or agent responsible for delivering this message to the intended recipient, please note that any dissemination, distribution, copying, or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message.