

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Value List not working
What am I doing wrong? I think maybe I just need another set of eyes to look at this as I can't find the error.
'Avg Order' will not show in the Value list. Below is a screen shot of the table and code. I also copy/pasted the code if that would make it easier to review. Thank you in advance.
Dimension: =ValueList('Order Count','Avg Order','Net Sales','NM%')
Expression:
if(ValueList('Order Count','Avg Order','Net Sales','NM%')='Order Count',
Num(count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'###,##0')
,
if(ValueList('Order Count','Avg Order','Net Sales','NM%')='Avg Order',
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'$###,###,##0')
,
if(ValueList('Order Count','Avg Order','Net Sales','NM%')='Net Sales',
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),'$###,###,##0')
,
Num( sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetMargin)
/ (sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetSales)),'###.#0%')
)))
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I take the "if" portion out of the Average Order formula, it works but I don't understand why... Any ideas?
Original:
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'$###,###,##0')
Modified:
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count(DISTINCT{<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} OrderNumber),'$###,###,##0')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I even tried a Pick(Match.. with the same results....
Pick(Match(
ValueList('Order Count', 'Avg Order', 'Net Sales', 'NM%'),
'Order Count', 'Avg Order', 'Net Sales', 'NM%'),
Num(count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'###,##0'),
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)/count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'$###,###,##0'),
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),'$###,###,##0'),
Num( sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetMargin) / (sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetSales)),'###.#0%'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@lfholland and with Pick Match ?
Pick(Match(ValueList('Order Count','Avg Order','Net Sales','NM%'),'Order Count','Avg Order','Net Sales','NM%'),
Num(count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'###,##0'),
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'$###,###,##0'),
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),'$###,###,##0'),
Num( sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetMargin)
/ (sum({<Year={$(=Max(Year))},TakenBy={'Ecom'},CurPrevYTD={1}>} NetSales)),'###.#0%')
)
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried the pick/match. I replaced the formula with an 'x' and it shows, so it must be the formula. BUT, I put the formula in a text object and it shows a value!!! I'm dyin'! lol

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
even without DC dimension ?
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yep!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I take the "if" portion out of the Average Order formula, it works but I don't understand why... Any ideas?
Original:
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>}distinct if(
aggr(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales),OrderNumber)>0,
OrderNumber)),'$###,###,##0')
Modified:
Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count(DISTINCT{<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} OrderNumber),'$###,###,##0')
