2 Replies Latest reply: Jan 13, 2016 10:05 AM by Dermot mcgoldrick RSS

    Listbox from a table expression?

    Dermot mcgoldrick

      Ok I have a straight table showing sales items and the following expression :

       

      if(sum({<Year = {"2015"}>} Quantity) > sum({<Year = {"2014"}>} Quantity) And sum({<Year = {"2014"}>} Quantity) > sum({<Year = {"2013"}>} Quantity) And sum({<Year = {"2013"}>} Quantity) > sum({<Year = {"2012"}>} Quantity)

      , 'Up', if(sum({<Year = {"2015"}>} Quantity) < sum({<Year = {"2014"}>} Quantity)  And sum({<Year = {"2014"}>} Quantity) < sum({<Year = {"2013"}>} Quantity) And sum({<Year = {"2013"}>} Quantity) < sum({<Year = {"2012"}>} Quantity), 'Down','Mixed'))

       

      Basically if sales have increased YOY then the trend is up, or down etc.  This works very well, however would I be better to do this in load script (and would filtering still work if I did) or better still can I create a listbox tied to this field in the table, ie showing "UP","DOWN" or "MIXED"?

       

      TIA for any advice.

       

        • Re: Listbox from a table expression?
          Stefan Wühl

          You can create a calculated dimension for your list box by creating a field <Expression> from field drop down list on general tab:

           

          =Aggr(

          if(sum({<Year = {"2015"}>} Quantity) > sum({<Year = {"2014"}>} Quantity) And sum({<Year = {"2014"}>} Quantity) > sum({<Year = {"2013"}>} Quantity) And sum({<Year = {"2013"}>} Quantity) > sum({<Year = {"2012"}>} Quantity)

          , 'Up', if(sum({<Year = {"2015"}>} Quantity) < sum({<Year = {"2014"}>} Quantity)  And sum({<Year = {"2014"}>} Quantity) < sum({<Year = {"2013"}>} Quantity) And sum({<Year = {"2013"}>} Quantity) < sum({<Year = {"2012"}>} Quantity), 'Down','Mixed'))

          , [YourSalesItemField]

          )

          • Re: Listbox from a table expression?
            Dermot mcgoldrick

            Worked great, thanks Stefan.