Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chris1987
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
sunny_talwar

Try this:

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

chris1987
Creator
Creator
Author

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

Chris

sunny_talwar

What is the problem? Can you elaborate?

chris1987
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

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