Set analysis to get the previous value isn't working

[Leandro_Silva]

Hello!

I am using a set analysis expression to calculate the sales for the previous quarter of the selection. Here is a sample of the data:

number_quarter	sales
4	5
4	4
4	2
3	4

The expression being used is this one: sum({$<number_quarter = {$(=Only(number_quarter)-1)}>} sales)

I was expecting that, when quarter Q4 is selected (not the same dimension as the number_quarter dimension, but also representing the quarter) then the sum of the sales for the previous quarter would be 4. But the formula is returning zero.

Anyone knows why this is happening? Also, in the edit expression screen the preview shows that there is any error (the $(=Only(number_quarter)-1) part of the formula shows 3 when quarter Q4 is selected.

[sonkumamon]

Try:

sum({$<number_quarter = {$(=Max(number_quarter)-1)}>} sales)

[Vegar]

As you are using Only(number_quarter), then you will only get output of you expression if you only have one [number_quarter] in your selection. With more than one number_quarter in your selection your only() function will return null.

But your expression should work as intended given your scenario. (I created a QV app that I attached)

[Leandro_Silva · Accepted solution]

Hi Vegar. Thanks for the answer.

At first i really thought that I wouldn’t need to select a number_quarter since a quarter was already selected.

But to overcome this I wrote the following expression: sum({$<quarter = P({1<number_quarter = {$(=Only(number_quarter)-1)}>})>} sales). The quarter dimension is the number quarter as a string (Q1, Q2, Q3 and Q4).

The P() function does the magic, since it consider values that are implied by the selection of the quarter.

Thanks again.

[Vegar]

Thats great that you figured out an soltion that worked for you.

You left the information about your [quarter] field out of your initial data sample. 

When creating a set expression you could consider each modifier as a selection in the datamodel. In your early example you where using the current selected set $. You where saying that you had made a selection on [quarter] field and with your set modifier/selection on number_quarter your where generating an non exsistent selection.  You where trying to calculate the sum of sales where quarter='Q4' and quarter_number=3. That combination will return zero, to get what you desire you will need to cancel the quarter selection from your $-set. You can do this like below.

sum({$<quarter=, number_quarter = {$(=Only(number_quarter)-1)}>} sales)

Your solution using p() will also work, but I consider my solution above less complex.