Valuelist Weirdness...

[#1]

Hi, and thank you in advance for having a think about this one! 🙂

We've been experiencing a really unusual issue with a seemingly simple Valuelist in one of our tables and we can't seem to fix it...

The following Valuelist is in place as the second dimension, to enable us to split calculation into current an previous financial year periods:

=ValueList('Current','Previous')

We then have the following expression:

=IF(ValueList('Current','Previous') = 'Current',

Sum( {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vMinDate)<=$(vMaxDate)'}>} [SalesOrderLine Value GROSS]),

Sum( {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vPreviousMinDate)<=$(vPreviousMaxDate)'}>} [SalesOrderLine Value GROSS])

)

The formulas within the expression work correctly on their own without the Valuelist function.

Now, you'd think that this would give a value (or null/zero) for each Value in the Valuelist, but it does not... with some primary dimension values, it defaults to the Previous value and populates both the Current and Previous values with the Previous. If we change the expression to Pick & Match, we get no values at all, suggesting that the Valulist dimension values do not exist, but only for some of the primary dimension values...

Does anyone have any idea why this might be happening? If this can't be fixed, it looks as though we'll have to do this the messy way and create and expression for each separate value, which is really a last resort...

Jenn

[JonnyPoole]

I haven't been able to get a valuelist or valueloop (synthetic dimension) to work with SET statements either unless the synthetic dimension returns only 1 value.  I'm not convinced its meant to work either at this point.

Perhaps you can model a new field for the dimension that splits your data between current and previous ( using a date calendar with period to date flags that will identify current vs previous values) rather than rely on a synthetic dimension.

Valueloop use incremented values

[#3]

Thanks Jonathan,

I had feared this might be the case 😞

Unfortunately, we can't used fixed values for periods in this instance, so getting the date ranges in the load wouldn't do the job.

We were hoping that the ValueList would allow us to split each Expression heading into two sub-headings on the pivot and it has worked for most of them and provides the correct overall total for each column. However, 7 out of the 96 1st dimension (Product Group) do not calculate correctly and bypass the ValueList, it's really odd (and irritating ^_^). Can't find anything different about the ones that don't work and they work without the ValueList, so can only assume that that's the cause.

So close, yet so far!

I suppose the only thing to do is to split the Expressions into two and make it a little messier...

[tresesco]

I guess valuelist() might not be the issue here, but something else, like, a confict with set analysis and your first dimension. Could you post sample qvw that demonstrates the issue?

[rubenmarin]

As tresesco, I guess valuelist() might work, I've done something similar.

Also i have to say that for previous year values I usually use Jonathan's approach, having those calculated in script, so it works even with conditional dimensions and other complex graphs.

[#6]

It would be pretty time consuming to produce a relevant dummy QWV for this, I'm afraid, as even the basic data set is quite complex and unless the data matches the same variances, I don't think it would show the same issue.

The only thing that I can see that is different about the non-working dimension values is that all but one of them have no value within the current min/max date range, but have a value in the previous min/max range. It appears that when the result of the "Current" formula is 0 or null, the whole ValueList fails for the dimension value. However, the anomaly here is that one of the dimension values that is not calculating HAS got a value associated with it (albeit a very low one, £17).

Any dimension values that have neither current nor previous range data show correctly as £0 in both - any that have no previous but a current show Null in the Previous.

[rubenmarin]

Hard to say without a sample to test, you can try something like:

=IF(ValueList('Current','Previous') = 'Current',

Sum( {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vMinDate)<=$(vMaxDate)'}>} [SalesOrderLine Value GROSS]),

Sum(TOTAL <Product> {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vPreviousMinDate)<=$(vPreviousMaxDate)'}>} [SalesOrderLine Value GROSS])

)

Assuming that product is your first and only dimension before valuelist.

[#8]

Logically, on further inspection, it looks as though it might have something to do with a conflict between the way the min/max ranges are calculated and the ValueList function:

We use a Listbox with dates in it, so that the user can select any historical date/date range and receive a current and previous sum for each Expression (this is date on previous date, not date on matching financial day, so in that regard it's fairly simple). This selection sets the min/max as one date, or a date range between which we look for data, using the SecondDate calendar  - the formula then looks on a FistDate basis at Sales, for example, within the date range.

It appears as though, when we introduce the ValueList, it stops the expression from seeing the FirstDate values where no data exists in the SecondDate calendar for that Product Group. E.g. we look at Lingerie for the 15th September and there is no data on a SecondDate basis, but at FirstDate level, there is £17 - with ValueList removed from the expression, the £17 is shown, but with ValueList splitting the expression, the £17 is not found.

[tresesco]

Preparing examples for Upload - Reduction and D... | Qlik Community

[#10]

Thanks tresesco, I might give this a go when I have some free time 🙂

At present, the QVW in question is 2.13GB in size and has many, many sheets and a spiderweb of tables making up the dataset, so it's not something I could do quickly. If I get some time at home, I'll try and do it then.