topic Re: Valuelist unexpected behaviour in QlikView

Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 14:09:45 GMT

Hi Folks,

I defined a Pivot table with a calculated Dimension (a valuelist) and the behaviour is rather curious.

A short example:

2 Dimensions (Region and Product), 1 calculated Dimension (Valuelist('Week','Year')

one Expression: sum Sales

What I want to Display is Region vertical, Valuelist in upper horizontal and Product in lower horizontal

Expression is defined as: if Valuelist('Week','Year')='Week', sum(Sales), sum ({<Week={'<=$(=max(Week))'}Sales)

I want to Display sales of selected week and cumulative sales (from start of year to selected week)

Header 1Region	Header 2	Header 3	Header 4	Header 5
	Week	Week	Year	Year
	Product 1	Product 2	Product 1	Product 2
Region1	10	20	50	100
Region 2	100	15	100	75

Now what is curious:
Whenever a Region has no sales, the expression does not calculate the first condition (Week) but the second one

So by simply defining if Valuelist('Week','Year')='Week',1,2 I do see 2 when there is no sales for this product

In the above table Product1 is not sold in Region 2, so Week contains the same value as Year

I cannot upload a small sample as upload is forbidden at customer site

the formulas (just an example) above are working (as all data is correct for regions with sales)

Any idea???

Re: Valuelist unexpected behaviour

swuehl — Wed, 01 Jun 2016 14:13:41 GMT

Have you also tried the same with a data island dimension created in the data model instead of ValueList()?

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 14:15:15 GMT

not yet, should it be a better approach than valuelist?

Re: Valuelist unexpected behaviour

Kushal_Chawda — Wed, 01 Jun 2016 14:28:05 GMT

try pick and match

Pick( match(Valuelist('Week','Year'),'Week','Year'),

sum(Sales), sum ({<Week={'<=$(=max(Week))'}Sales))

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 14:52:30 GMT

now week data seems to be okay (Shows "-" when there is no data)

but year data is not correct, total shows correct value, but only 1 product shows

correct data while other products Show "-"

Re: Valuelist unexpected behaviour

Kushal_Chawda — Wed, 01 Jun 2016 16:39:42 GMT

Would you be able to share sample?

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 19:28:05 GMT

I will prepare a sample and upload it.

it may take a while

what i noticed with your pick method is that i got Detail values for year onlyvfor

the product which alslo had sales in the week.

the total was correct in both cases

Re: Valuelist unexpected behaviour

swuehl — Wed, 01 Jun 2016 19:51:30 GMT

Have you already tried the data island? I think it may help with your issue:

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 20:03:23 GMT

I attach example with both ways (Valuelist and Data Island).Please have a look to PROD3. In week 11 there is no sales for REG2. In the Pivot table we see the value for the year

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 20:26:41 GMT

please see attached file

hope you find a solution

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 20:28:21 GMT

see attached file with both qays (valielist and data Island - Suggestion from swuehl)

PROD2 does not have a value in week 11 for REG2

Re: Valuelist unexpected behaviour

sunny_talwar — Wed, 01 Jun 2016 20:31:17 GMT

May be this:

If(Valuelist('Week','Year')='Week' or Len(Trim(Valuelist('Week','Year'))) = 0,

sum(Sales),

sum({<Week={'<=$(=max(Week))'}>}Sales))

UPDATE:
I think the issue is that ValueList() becomes null for some reasons and the if goes directly to the false statement

Re: Valuelist unexpected behaviour

swuehl — Wed, 01 Jun 2016 20:32:53 GMT

Maybe like this

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 20:49:23 GMT

amazing, with Expression len(... )=0 I get only table with PROD2

( I have QV 12, my custo´mer have QV 11.20 SR 12)

In your upper table I miss the cumulated values (year) for PROD1 and PROD3 as they had been sold in Week 5 and 10; only for PROD2 the calculation works

Re: Valuelist unexpected behaviour

Anonymous — Wed, 01 Jun 2016 20:57:55 GMT

your solution with if (Only(TOTAL<Dim> Dim)='WeekA',

works (at least with the small example).

I will test it next monday at my customer site

thanks

Re: Valuelist unexpected behaviour

sunny_talwar — Wed, 01 Jun 2016 21:01:28 GMT

Here is another try

If(Valuelist('Week','Year')='Week' or (Len(Trim(Valuelist('Week','Year'))) = 0 and (ColumnNo(TOTAL) * Avg({1} 1)) <= Count({<Week>}DISTINCT TOTAL Week)),

sum(Sales),

sum({<Week={'<=$(=max(Week))'}>}Sales))