Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Help with Embedded Function - Set Analysis

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

11 Replies
Highlighted

Try this:

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

Highlighted
Creator
Creator

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

Chris

Highlighted

What is the problem? Can you elaborate?

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted

Did you check if this works or not?

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

or this

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

Highlighted
Creator
Creator

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