0 Replies Latest reply: Sep 9, 2016 6:04 AM by ana sulistrowski RSS

    Conditions in Set Analysis

    ana sulistrowski

      I am having some trouble including a condition in my set analysis.

       

      Goal: count the products id from my max month year ( it works), for a certain level type (also works), and for when the field value is greater than the 3% of the total value sum (with the same filters). This is where I am having trouble. The field value can be positive and negative, but I need to ignore it and just use absolutes, the fabs.

       

      If I use the following set analysis, it will only give back the values ( not absolute values) that are grreater than the 3% of total value (colored in orange in the image), which is almost what i want, i just need to include the absolute values somewhere. It is counting 2 ids instead of 7 (see image):

       

      =count({<

      month={"$(=Max(month))"},

      [Nivel 3]={'No explicadas'},

      Valor={"> $(=sum({<month={'$(=Max(month))'},[Nivel 3]={'No explicadas'}>}fabs(Valor)) *0.03)"}

      >}ID_operacion)

      Captura.PNG

      I have also tried the following set analysis, using the 3% of the total sum as a number, and it is counting the 7 ids.

       

      =count({<month={"$(=Max(month))"},

      [Nivel 3]={'No explicadas'},

      Valor={"=fabs(Valor) > 2235184.339 "}

      >}ID_operacion)

       

       

      However, when i mix them both, they dont work:

       

      =count({<month={"$(=Max(month))"},

      [Nivel 3]={'No explicadas'},

      Valor={"=fabs(Valor) > $(=sum({<month={'$(=Max(month))'},[Nivel 3]={'No explicadas'}>}fabs(Valor)) *0.03)"}

      >}ID_operacion)


      Any suggestions about how to solve this?

       

       

      thanks!