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: 
vyacheslavg
Contributor III
Contributor III

Simple set analysis (dates)

Hello everyone,

Asking for help with cracking of set analysis.

I need to show number of products, which were created _before_ specific year.

But... below expression works fine and shows correct result.

Count ({$<Product.Year] ={"2017"}>} ProductID)

If I change 2017 to <2017, like this

Count ({$<Product.Year] ={"<2017"}>} ProductID)

it does not work... Shows zero, 0.

Example of data set below.

Product.Year is recognized by Qliksense as date, no problems in other date related calculations.

Bookmarks for selection work as well, but I need set analysis to work to use in more advanced sets.

ProductID Product.Year

1     2013

2     2017

3     2015

4     2017

1 Solution

Accepted Solutions
OmarBenSalem

Have no clue.. what I would suggest is creating a year field in the script:

year([Expiry Date]) as myYear;

then use this new field :

Count ({$ <myYear= { "<=2017"}>} ProductID)

that would work

View solution in original post

13 Replies
MK_QSL
MVP
MVP

=COUNT({<[Product.Year]= {'<2017'}>}ProductID)

ahaahaaha
Partner - Master
Partner - Master

Count ({$ <[Product.Year] = { "<2017"}>} ProductID)

No brackets (highlighted in red)

OmarBenSalem

Count ({<[Product.Year] ={"<=2016"}>} ProductID)

You forgot the [

See the attached app:

used expression:

Sum({<Year={"<=2015"}>}Sales)



vyacheslavg
Contributor III
Contributor III
Author

Guys, I'm sorry to miss the square bracket during copy/paste.

Yes, I have the bracket in place. Otherwise the initial expression will not work.

So,

Count ({$ <[Product.Year] = { "2017"}>} ProductID) - works

Count ({$ <[Product.Year] = { 2017}>} ProductID) - also works


But

Count ({$ <[Product.Year] = { "<2017"}>} ProductID) - does not work!


Please advise what could be the reason? I'm completely puzzled.

If needed, I can attach the qvf with mock data.



OmarBenSalem

please do

vyacheslavg
Contributor III
Contributor III
Author

vyacheslavg
Contributor III
Contributor III
Author

I've found what _is_working

Count ({$ <[Expiry Date.autoCalendar.Date] = { ">=01/01/2017"}>} ProductID) - this works fine.

Still not working with .Year - maybe there is some autocalendar trick or issue in Qliksense?

OmarBenSalem

Does this make the work?

When you select a year, you'll have your measure till the seleced year:

Capture.PNG

See the attached app

vyacheslavg
Contributor III
Contributor III
Author

I like your approach, it solves the part of problem, though not all - thank you very much!

In future I will need a combo of many sets inside the set analysis expression, and interactive selection approach will not work.

Thanks for advice in any case.

In any case, need to go deeper in set analysis