Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]
)
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]
)
Worked great, thanks Stefan.