Help with Embedded Function - Set Analysis

[chris1987]

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

[sunny_talwar]

Try this:

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

[chris1987]

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

Chris

[sunny_talwar]

What is the problem? Can you elaborate?

[chris1987]

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

[sunny_talwar]

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])

[chris1987]

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

[Peter_Cammaert]

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")

[sunny_talwar]

Did you check if this works or not?

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

or this

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

[chris1987]

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