topic Re: Help with Embedded Function - Set Analysis in QlikView

Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 11:07:31 GMT

Hi,

I'm trying to use a function in set analysis without any luck.

The function I'm trying to use is :

=only({<CreatedDate={'=today()'}>}[WeekPeriod])

which returns 201812 (as we're currently in week12 or year 2018)

I'm trying to embed it in this function:

=sum({<[I_Txn-type]={'INV'},[WeekPeriod]={"=only({<CreatedDate={'=today()'}>}[WeekPeriod])"}>}[IL_Net-ext])

but I don't get correct results.

I've tried using single quotes and double quote - the [WeekPeriod]={"=only({<CreatedDate={'=today()'} is just getting ignored all together.

Thanks

Chris

Re: Help with Embedded Function - Set Analysis

sunny_talwar — Tue, 20 Mar 2018 11:09:41 GMT

Try this:

=Sum({<[I_Txn-type] = {'INV'}, [WeekPeriod] = {"$(=Only({<CreatedDate = {'$(=Today())'}>} [WeekPeriod]))"}>} [IL_Net-ext])

Re: Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 11:23:04 GMT

Thanks Sunny, but unfortunately it's still the same problem.

Chris

Re: Help with Embedded Function - Set Analysis

sunny_talwar — Tue, 20 Mar 2018 11:24:49 GMT

What is the problem? Can you elaborate?

Re: Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 11:33:23 GMT

Here is my expression, If I enter the WeekPeriod manually I get 189,442:

=sum({<[I_Txn-type]={'INV'},[WeekPeriod]={'201812'}>}[IL_Net-ext])

but if I apply the ONLY() logic I get 0:

=sum({<[I_Txn-type]={'INV'},[WeekPeriod]={"$(=Only({<CreatedDate = {'$(=Today())'}>} [WeekPeriod]))"}>}[IL_Net-ext])

and If I put =Only({<CreatedDate = {'$(=Today())'}>} [WeekPeriod])) into a text box I get 201812 so I know the formula is working correctly.

Thanks

Chris

Re: Help with Embedded Function - Set Analysis

sunny_talwar — Tue, 20 Mar 2018 11:37:20 GMT

I am not seeing any issue with the expression... can you try this

=Sum({<[I_Txn-type] = {'INV'}, [WeekPeriod] = {"$(=MinString({<CreatedDate = {'$(=Today())'}>} [WeekPeriod]))"}>} [IL_Net-ext])

Re: Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 12:06:44 GMT

Thanks Sunny,

I'm going to look more into this, we've just upgraded to 12.2 and the function I was using:

=only({<CreatedDate={'=today()'}>}[WeekPeriod])

is no longer working, although I've been testing with this all morning.

I'll post an update later today

Thanks

Chris

Re: Help with Embedded Function - Set Analysis

Peter_Cammaert — Tue, 20 Mar 2018 12:27:52 GMT

QV12.20 is much stricter when handling single quotes and double quotes in Set Analysis expressions. Maybe that's the reason why it doesn't work anymore.

Check the QlikView November 2017 Release notes here: New November Releases - Read All About It Here! (page 3 - "Quotes in Set Analysis expressions")

Re: Help with Embedded Function - Set Analysis

sunny_talwar — Tue, 20 Mar 2018 12:36:03 GMT

Did you check if this works or not?

=MinString({<CreatedDate = {'$(=Today())'}>} [WeekPeriod])

or this

=MinString({<CreatedDate = {'$(=Date(Today()))'}>} [WeekPeriod])

Re: Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 13:43:58 GMT

Thanks for your help Sunny,

This ended up working in the end (as you can see I changed to a numeric date ID) but you pointed me in the right direction

=sum({<[I_Txn-type]={'INV'},[WeekPeriod]={"=only({<DateID={'$(=num(today())-1)'}>}WeekPeriod)"}>}[IL_Net-ext])

Chris

Re: Help with Embedded Function - Set Analysis

sunny_talwar — Tue, 20 Mar 2018 13:45:06 GMT

Awesome

Re: Help with Embedded Function - Set Analysis

chris1987 — Tue, 20 Mar 2018 13:47:22 GMT

Thanks Peter, that's really useful!