topic Re: Value List not working in QlikView

Value List not working

lfholland — Wed, 16 Dec 2020 18:41:44 GMT

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

Re: Value List not working

lfholland — Wed, 16 Dec 2020 18:56:27 GMT

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

Re: Value List not working

Taoufiq_Zarra — Wed, 16 Dec 2020 19:00:12 GMT

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

Re: Value List not working

lfholland — Wed, 16 Dec 2020 19:02:27 GMT

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

Re: Value List not working

Taoufiq_Zarra — Wed, 16 Dec 2020 19:11:51 GMT

even without DC dimension ?

Re: Value List not working

lfholland — Wed, 16 Dec 2020 20:59:12 GMT

Yep!

Re: Value List not working

lfholland — Wed, 16 Dec 2020 21:27:33 GMT

If I take the "if" portion out of the Average Order formula, it works but I don't understand why... Any ideas?

Original:

Modified:

Num(sum({<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} NetSales)
/
count(DISTINCT{<Year={$(=Max(Year))},TakenBy={'Ecom'}, CurPrevYTD={1}>} OrderNumber),'$###,###,##0')