topic Re: Set analysis to get the previous value isn't working in Search the Community

Set analysis to get the previous value isn't working

Leandro_Silva — Mon, 01 Feb 2021 17:34:54 GMT

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.

Re: Set analysis to get the previous value isn't working

sonkumamon — Mon, 01 Feb 2021 17:32:27 GMT

Try:

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

Re: Set analysis to get the previous value isn't working

Vegar — Mon, 01 Feb 2021 17:39:34 GMT

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)

Re: Set analysis to get the previous value isn't working

Leandro_Silva — Mon, 01 Feb 2021 19:18:58 GMT

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.

Re: Set analysis to get the previous value isn't working

Vegar — Mon, 01 Feb 2021 20:35:46 GMT

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.