topic Re: Question about expression in QlikView

Question about expression

ali_hijazi — Wed, 07 Dec 2016 14:26:37 GMT

Hello which approach is better?

if( vSelectedCurrency = 'USD'

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostUSD)

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostLBP)

)

SUM({<TrxType={'Sales'},DocType={'I'}>} if( vSelectedCurrency = 'USD', CostUSD, CostLBP))

Re: Question about expression

sunny_talwar — Wed, 07 Dec 2016 14:31:32 GMT

johnw‌ did some testing and it seems that from his testing results

if( vSelectedCurrency = 'USD'

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostUSD)

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostLBP)

)

should be better

Re: IF vs Pick Match

Re: Question about expression

sunny_talwar — Wed, 07 Dec 2016 14:32:59 GMT

But I would suggest you to do your own testing as well.

Re: Question about expression

Gysbert_Wassenaar — Wed, 07 Dec 2016 14:33:57 GMT

This one:

Pick(Match( $(vSelectedCurrency), 'USD')+1,

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostLBP)

,SUM({<TrxType={'Sales'},DocType={'I'}>}CostUSD)

)

Re: Question about expression

vinieme12 — Wed, 07 Dec 2016 14:35:29 GMT

Try it yourself

for each expression check the calc time in Sheet Properties >> Objects tab

try applying multiple filters and repeat the same with the other expression

Re: Question about expression

johnw — Wed, 07 Dec 2016 16:54:54 GMT

Be very careful when doing this. QlikView is a master at caching results or partial results. When I check calc times, I always do so by first closing QlikView to clear the cache, then reopening QlikView, reopening my document, and then going to the chart. Be careful even then, as you might have something on the tab you open that could cache data for the chart of interest. So I ALSO open to an empty tab, and then switch to a tab that has nothing but the object of interest. Also do it a few times to see if the CalcTime stays steady, or varies a lot. All those warnings aside, yes, the CalcTime is very valuable for performance tuning.

Re: Question about expression

sunny_talwar — Wed, 07 Dec 2016 16:57:59 GMT

I wish there was a way to automate this kind of performance testing. I guess Rob's Document Analyzer kind of does this, but I would have liked a tool just for performance testing

Re: Question about expression

johnw — Wed, 07 Dec 2016 17:17:17 GMT

Yep, likely better based on what I saw, and on my assumptions (not knowledge) about how QlikView works. Also agreed that you should test it yourself. But there would be even faster approaches involving data model changes if neither performs well enough. I'm not at all an expert on regionalization and currencies, but I'd think you'd want to have an actual Currency field that connects you to a Cost, rather than having separate costs for each currency. Something like this as a new table added to your data model:

Transaction ID, Currency, Cost
12345, USD, 500
12345, LBP, 750000
23456, USD, 100
23456, LBP, 150000

Currency, Format, any other currency information
USD, $#,##0.00;($#,##0.00)
LBP, ‎#,##0LBP;-‎#,##0LBP

And use the expression:

money(sum({<TrxType={'Sales'},DocType={'I'}>} Cost),Format)

You probably won't notice any performance difference, but at least it's a simpler expression. For performance, maybe extend it like this:

Transaction ID, Currency, Cost, SalesICost
12345, USD, 500, 500
12345, LBP, 750000, 750000
23456, USD, 100, null()
23456, LBP, 150000, null()

And use the expression:

money(sum(SalesICost),Format)

I suspect it won't run much faster, but I suspect it will still at least be measurably faster. But mostly, I prefer my complexity be in the script rather than in the expressions.

But again, I'm not even close to an expert in regionalization and currencies. Our company does all business and all reporting in a single currency, a single language. You could probably find better suggestions than the above with some searching on the forum for how people have handled currencies.

Re: Question about expression

johnw — Wed, 07 Dec 2016 17:27:23 GMT

It might be easier than what I'm doing. I think you can turn off caching, and then I'm guessing all your calc times would be comparable. Set caching to 0% on the User preferences, general, working set limits. The help text seems to suggest that would work, but I've not tried it, and I'm not certain. Definitely remember to change it back when you're done, or you'll be wondering why QlikView is suddenly so slow!

I see that Rob's Document Analyzer has calc times for the objects, but I imagine those are the actual calc times, not the calc times with caching off.

Hmmm, though maybe caching off isn't what we want either. Some expressions may, in and of themselves, build and used cached data, like if(sum(Sales),sum(Sales)). With caching, it would only do the sum once. Without, I'm guessing it would be forced to do it twice, which isn't what we're looking to see. Ugh. So maybe leaving caching on and how I'm doing it is the best we can do. I'd like there to be a better way.