Point in time using Set Analysis

[tim_m]

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!

[jwjackso · Accepted solution]

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]

Hi Jerry,

Thanks a lot, it works

[tim_m]

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]

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.