Listbox from a table expression?

[dmac1971]

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.

[swuehl · Accepted solution]

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]

)

[dmac1971]

Worked great, thanks Stefan.