5 Replies Latest reply on Aug 31, 2018 1:54 AM by Guillonneau Antony

    Replacing 0 revenue values with category's median

    Guillonneau Antony

      Hi guys,


      I need some help with something I have been spending hours on.


      In my data set, I have missing revenue values (scrapping data), that I want to replace with some king of average/median when data is missing.


      Only thing is, I have differents average/median depending on the category/tag associated with.


      For example, if the company is tagged as "potential premium", I want to apply an estimated revenue of $200K.

      If the company is tagged as "small but dynamic", revenue applied would be $100K.


      When I try to do a calculated field, it doesn't work. I've tried LOD (maybe I don't know how to use them very well), multiple if (shows an error with the AND function).

      What I've tried :


      {FIXED: (IF ([category = "potential premium"]) AND ISNULL([Ca 2016 Y]))

      THEN 200000 ELSE [Ca 2016 Y] END)}


      IF ([category] = "potential premium")

      AND ISNULL([Ca 2016 Y])

      THEN 200000

      ELSE [Ca 2016 Y] END


      Can someone help ?