topic Re: Set expression being ignored in QlikView

Set expression being ignored

Wed, 28 Oct 2015 14:53:49 GMT

I have a list of items with prices in different currencies, each item in a class which is used as the dimension. I want to display the totals by class in a selected display currency. And I want to do this with (A) the current selection {$}, and (B) the entire set {1}.

The load statements are as follows:

Data:

LOAD * INLINE [

Class,Item,Price,Currency

'Tech','TV',200,'GBP'

'Tech','VCR',100,'EUR'

'Tech','Speakers',400,'USD'

'Home','Sofa',800,'GBP'

'Home','Table',900,'EUR'

'Home','Rug',200,'USD'

'Garden','Swing',150,'GBP'

'Garden','Parasol',250,'GBP'

'Garden','Mower',300,'GBP'

];

Currencies:

LOAD * INLINE [

Currency,ExchangeRate

USD, 1.00

EUR, 0.90

GBP, 0.65

];

DisplayCurrencies:

LOAD * INLINE [

DisplayCurrency,DisplayRate

USD, 1.00

EUR, 0.90

GBP, 0.65

];

My expressions are respectively:

(A): DisplayRate*Sum(Price/ExchangeRate)

(B): DisplayRate*Sum({1}Price/ExchangeRate)

When I select different combinations of Class the first chart behaves as expected, displaying the selected dimensions.

But the second chart changes in synch with the first whereas I expect it to show all dimensions all the time.

If I remove the scalar DisplayRate from the second expression to become

Sum({1}Price/ExchangeRate)

then it displays all classes all the time, as I expect.

Is there some subtlety in set expressions that I have missed?

I am attaching my QVW file.

Re: Set expression being ignored

Gysbert_Wassenaar — Wed, 28 Oct 2015 17:50:29 GMT

DisplayRate is also aggregated using the implicit Only() function since you didn't specify an aggregation function explicitly. Your expression is really Only(DisplayRate)*Sum(Price/ExchangeRate). To make that ignore selections as well you need to add the record set definition {1} to the Only() function too: Only({1}DisplayRate)*Sum({1}Price/ExchangeRate)

Re: Set expression being ignored

Wed, 28 Oct 2015 17:55:27 GMT

Try this expression

=sum({1} {<DisplayRate,Class>} DisplayRate*Price/ExchangeRate)

Re: Set expression being ignored

Wed, 28 Oct 2015 18:22:03 GMT

I don't understand the syntax of the inner {..}.

What I did try was

$(=DisplayRate)*Sum({1}Price/ExchangeRate)

and this worked.

So now I want to understand how dollar-sign expansions affect the logic.

Re: Set expression being ignored

Wed, 28 Oct 2015 18:25:56 GMT

This gives "no data to display"! But surely since there is only one value selected, Only() should be identical whatever the set identifier?

Re: Set expression being ignored

Gysbert_Wassenaar — Wed, 28 Oct 2015 18:39:03 GMT

Can you post a small qlikview document that demonstrates the problem?

Re: Set expression being ignored

Wed, 28 Oct 2015 18:40:20 GMT

It's attached to the initial entry.

Re: Set expression being ignored

swuehl — Wed, 28 Oct 2015 19:06:16 GMT

Kind of strange.

I got it working using a variable vRate = DisplayRate

Re: Set expression being ignored

Wed, 28 Oct 2015 19:12:32 GMT

Of course this is a contrived example to illustrate a difficulty I have found on a bigger data set. So I am interested in the principles of what works and why.

What is the avg() function doing that is different from just using a single-valued field? In another post to this thread I tried $(=DisplayRate) and this worked. I can use a constant scalar such as 0.5*Sum(...) so what type of entity can I use instead of the 0.5?

Re: Set expression being ignored

Gysbert_Wassenaar — Wed, 28 Oct 2015 19:35:56 GMT

Yeah, I can understand why only({1}DisplayRate)*... wouldn't work. But only({<Class>}DisplayRate)*... should afaik. Perhaps the fact that the tables are not linked is causing the weirdness. As soon as I outer join DisplayCurrencies with the Currencies table the weirdness is gone. Of course the expression results are bogus after the outer join (though sum(distinct...) works). My conclusion: use data islands with care. They're inhabited by gremlins.

Re: Set expression being ignored

Wed, 28 Oct 2015 19:45:44 GMT

Point taken! And I like the idea of setting a variable to the value from the data island to ensure it is a scalar. That is the point of my island in the first place. I am still getting my head around the associative data model as opposed to the relational third normal form I have used all these years!

Re: Set expression being ignored

Wed, 28 Oct 2015 20:23:58 GMT

This one works please try this

Re: Set expression being ignored

Thu, 29 Oct 2015 13:52:55 GMT

One final question. I don't recognize the syntax in your expression - only({<Class>}DisplayRate)

What does the set modifier <Class> do? I have seen <Class=> but not just the fieldname alone.

Is there a good reference for this?

Thanks.

Re: Set expression being ignored

swuehl — Thu, 29 Oct 2015 15:37:26 GMT

I believe <Class=> and <Class> are functional equivalent. Both clear selections in field Class.