Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
=COUNT({<[Product.Year]= {'<2017'}>}ProductID)
Count ({$ <[Product.Year] = { "<2017"}>} ProductID)
No brackets (highlighted in red)
Count ({<[Product.Year] ={"<=2016"}>} ProductID)
You forgot the [
See the attached app:
used expression:
Sum({<Year={"<=2015"}>}Sales)
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.
please do
Here is the qvf and excel links.
It looks I don't have rights to attach files.
https://www.dropbox.com/s/6gssl1tri6cw0sz/Mockup%20Product.qvf?dl=0
https://www.dropbox.com/s/jkfmw7ob2zyyzzo/Product%20expiry%20date.xlsx?dl=0
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?
Does this make the work?
When you select a year, you'll have your measure till the seleced year:
See the attached app
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