12 Replies Latest reply: Feb 2, 2018 6:46 AM by Liv ma RSS

    Calculated dimensions as set modifiers

    Liv ma

      Hello

       

      I hope I'll be able to explain what I want to achieve, so here it goes:

       

      1. database fields: reporting_date, client, numerical_attribute, sales

       

      2. calculated dimension: for any 2 selected dates, if the numerical attribute of the client has increased, I define a new category named, let's say, "UP", if it has decreased then its "DOWN", stayed the same is "NO CHG"

       

      since I want to be able to calculated this dimension for any 2 reporting dates upon making a selection, I use in the dimension as set modifiers 2 variables based on the reporting date selected (one variable calculates the largest date and the other the smaller date)

       

      so basically my dimension calculates, for any 2 selected reporting dates and for each client, whether the numerical attribute has increased or decreased and assigns to the client either an UP or a DOWN or a NO CHG

       

      I cannot implement this in the script because whether the client is included in UP/DOWN/NO CHG depends on what 2 dates are compared, i.e. the same client can be in eg UP when 2 dates are selected and it can be in eg DOWN when another 2 dates are selected

       

      3. what I want to do is calculate total sales (or increase in sales, it doesn't matter) for the clients which, for 2 dates, are in the UP category and only in this category (or in the DOWN etc, but only for that category)

       

      if the dimension I calculate was a field, I would use a normal modifier sum({<dimension={'UP'}>}sales)

       

      but if instead of "dimension" I write the formula for the dimension, it does not work

       

      if I put the calculated dimension in a table, it works, but I need the value only for each category at a time (for a waterfall chart)