2 Replies Latest reply on Nov 26, 2016 1:36 AM by Luca Pellegrino

    Obtaining the first reference matching a condition

    Luca Pellegrino

      Hi all,

       

      this is my first question on the community, so I hope to be as clear as possible.

       

      In the attached example file, I have a serie of players ("Publisher") with their (cumulated) corresponding KPI value at a certain "Frequency" value.

      Frequency is a continous Dimension.

       

      What I'd like to do is to wirte a calculated filed that returns the first Frequency value for which each Publisher exceed a certain KPI value, let's say 80%.

      So in the attached file this function should return "2" for the first Publisher "26" for the second, and so on.

       

      At first I tried to use the last() and first() fuctions, combined with a if() that mathces exceeding and not exceeding values, but it didn't work.

      I'm quite sure lookup() function can do the trick, but I need some help in figuring out how to use it in the calculated field.

       

      Thanks in advance to anyone who is going to help me on that.

       

      Cheers

       

      Luca

        • 1. Re: Obtaining the first reference matching a condition
          Sarah Ebreo

          Hi Luca,

           

          After pivoting the date you provided I was able to return the first Frequency value for which each Publisher exceed the 80% KPI value. I attached a sample workbook that illustrates the solution below.

           

          1. In the Data Connection window, ctl+click the publisher columns, right click and select Pivot.
          2. Create a calculated field named Exceeded KPI using the formula: WINDOW_MAX(RUNNING_SUM(IF SUM([KPI]) > .8 THEN 0 ELSE 1 END) + 1)
          3. Create another calculated field named Index Filter using the formula: INDEX()=1
          4. Place Publisher on Columns, Frequency on Rows, Exceeded KPI on Text, and Index Filter on Detail
          5. Right click Exceeded KPI and Index Filter on the view and select Compute Using > Frequency
          6. Move Index Filter from Detail to the Filters shelf, select True, and click OK
          7. Move Frequency from the Rows shelf to Detail.

           

          In the attached workbook, I broke out Exceeded KPI calculation into three separate fields so you can see how the formula is working.

           

          I am sure there is a prettier solution out there, but I hope this helps in the meantime! Let me know if you have any questions about these steps.

           

          Sarah

          1 of 1 people found this helpful
          • 2. Re: Obtaining the first reference matching a condition
            Luca Pellegrino

            Hi Sarah,

             

            you're solution does the magic.

            Thanks a lot.

             

            It's perfect for my actual needs.

            I didn't know about the Pivot function, and I think I'm gonna definitely learn more about it, since it seams it can came in handy in a lot of situations.

             

            Thanks again

             

            Luca