13 Replies Latest reply: Apr 6, 2017 4:29 PM by vyacheslav guzanov

# 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

• ###### Re: Simple set analysis (dates)

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

• ###### Re: Simple set analysis (dates)

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

No brackets (highlighted in red)

• ###### Re: Simple set analysis (dates)

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

You forgot the [

See the attached app:

used expression:

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

• ###### Re: Simple set analysis (dates)

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!

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

• ###### Re: Simple set analysis (dates)

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?

• ###### Re: Simple set analysis (dates)

Does this make the work?

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

See the attached app

• ###### Re: Simple set analysis (dates)

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

• ###### Re: Simple set analysis (dates)

What's the part not solved yet?

• ###### Re: Simple set analysis (dates)

I want to work this expression

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

If  Count ({\$ <[Product.Year] = { "2017"}>} ProductID) works for 2017, what is the problem with above (= show all productsId, where years are less than 2017?)

In other words, if 2017 works, why <2017 does not work?

• ###### Re: Simple set analysis (dates)

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

• ###### Re: Simple set analysis (dates)

thank you so much - this simple approach works perfectly.

It looks like my trouble was related to some issue with autocalendar transformations.

I was going in mad circles trying to understand what is going on...