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: 
tim_m
Partner - Contributor III
Partner - Contributor III

Point in time using Set Analysis

Hi Folks,

I drag a line chart with Year and Products as a Dimension and the following expression Sales:

sum({<Year={">=$(=MAX(Year)-1)<=$(=MAX(Year))"}>}VALUE)

Now I get all of my Products you see here: I like to select three of my products, not all. How should I change my expression? Thanks!

1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

Try

sum({<Year={">=$(=MAX(Year)-1)<=$(=MAX(Year))"},Products={'Prod1','Prod2','Prod3'}>}VALUE)


where Products is the dimension and Prod1 is a product that you want to display

View solution in original post

4 Replies
jwjackso
Specialist III
Specialist III

Try

sum({<Year={">=$(=MAX(Year)-1)<=$(=MAX(Year))"},Products={'Prod1','Prod2','Prod3'}>}VALUE)


where Products is the dimension and Prod1 is a product that you want to display

tim_m
Partner - Contributor III
Partner - Contributor III
Author

Hi Jerry,

Thanks a lot, it works

tim_m
Partner - Contributor III
Partner - Contributor III
Author

Hi Jerry,

now I get something like this:

But I want both lines ending in 2017. But I am doing wrong?

Additionaly I want to create two charts like this. One place the rising sales from 2016 to 2017 and the second chart only the falling sales from my Products. How could I do that?

jwjackso
Specialist III
Specialist III

I'm guessing Year is coming from a calendar.  You could replace =MAX(Year) with a variable that is set to a 2017.


For the second, you use an If test.  Check it the product sales is greater in 2016 than 2017.  Something like:  If(Sum({<Year={'2016'}>} VALUE) > Sum({<Year={'2017'}>} VALUE),1,0).  Hide the expression, suppress zero.  That should be all the rising products.  Reverse it to find the falling products.