Value List not working

[lfholland]

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%')
)))

[lfholland]

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%'))

[Taoufiq_Zarra]

@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%')

    )

[lfholland]

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

[Taoufiq_Zarra]

even without DC dimension ?

[lfholland]

Yep!

[lfholland · Accepted solution]

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')