3 Replies Latest reply: Feb 10, 2017 8:46 AM by Marie Holtz RSS

    Min Max function

    Marie Holtz

      Good morning everyone,

       

      I am new in Qlik Sense and I would like to reproduce a formula from Excel in my Qlik Sense script

       

      The formula I am trying to edit from Excel is =MIN(MAX((A1-B2)/B2),1)

       

      Where A1 in Qlik is (Sum({<CurYTDFlag={1}, Year={$(=Year($(vMaxDate)))}>}[TR])

      and B2 in Qlik is (Sum([MC])*max({<TRYTDFlag={1} >}[Quota]))

       

      Could you please help me out ?

       

      Thank you

       

      Marie

        • Re: Min Max function
          Andrey Khoronenko

          Hi Marie,

           

          You can see at least a little bit the source data, and that you want to get as a result?

           

          Regards,

          Andrey

            • Re: Min Max function
              Marie Holtz

              Hello Andrey,

               

              Thank you for your answer. Here is a little bit more info about the formula.

               

              A1 is represents revenue (TR) and B2 is represents the costs (MC). The formula in Excel returns an amount between -1 and 1 (that is why we have MAX and MIN). The YTD Flags are fixing the amounts in the current year, both A1 and B2 in Qlik Sense are returning correct results, the error appears when I try to include the Min and Max functions.

               

              I don’t have a qvs file, but for example, if A1 is 150 and B2 is 40, the formula will bring 1.

              If A1 is 60 and B2 is 80, the formula will bring 0.25.


              I hope it helps.


              Thanks again


              Marie

            • Re: Min Max function
              Anil Babu Samineni

              I am just assuming Your Value

               

              If A1 Gives 100 and B2 gives 20 then your formula seems this

               

              =MIN(MAX((A1-B2)/B2),1)


              Max((100-50)/50) -- Max(50/50) -- Max(1) -- 1


              Min(1, 1) -- 1 Only , Here what was the logic behind this. I doubt i am follow you or not 100% Accuracy. Would you describe more