Set Analysis - second selection?

[#1]

Hi all,

I have following expression:

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>} $(varGrossSales))

It returns me the full set of records of variable Gross Sales, with the modifier selected year -1.

Now I want to take into consideration the country code. If I select the country, the result set should also be changed.

Is it possible to insert a new modifier: consider in the selection the year-1 and e.g.the country code.

I have tried this, but this does not work:

=-SUM( {1<TRANSYEAR={$(=Only(TRANSYEAR)-1)}>*countrycode} $(varGrossSales))

Thank you in advance.

Regards Christoph

[#2]

Hi, yes you are right. We are very close tho the solution.

The suggested expression works to 90%, but somewhere there must be an error, because of the red underline (perhaps with the brackets. ) and a missing value for a customer

I have checked different combinations in the formula, but I didn't get it. I am sorry, but I am new to this.

Do you have an idea?

[swuehl · Accepted solution]

I believe the red underline is a bug in the syntax checker (with the empty p() function, you could use the equivalent p(%COUNTRYID) to get rid of the red underline).

But I updated the expression to include the restriction in TRANSYEAR also in the p() function, see above. This should resolve the missing value.

[gandalfgray]

Hi again

There is more than one way to do it but

what I normally prefer to do is stuff like:

Sum( {$<dim1={whatever}, dim2=, dim3=>}  amount)

That is I use the current selection, but sets dim1 to another value of my choice, and unsets any choices in dim2 and dim3

(You often need to use this technique when dealing with year, month, date etc which depends on eachother)

[#5]

Hi swuehl,

that's it. A bug is also possible, but I get it with your suggestion.

The missing value is existing, but I could imagine, it is a problem of data and not relevant in this case.

Thank you very much for your help, I have learnt a lot.

CH

swuehl schrieb:

I believe the red underline is a bug in the syntax checker (with the empty p() function, you could use the equivalent p(%COUNTRYID) to get rid of the red underline).

But I updated the expression to include the restriction in TRANSYEAR also in the p() function, see above. This should resolve the missing value.

[#6]

Hi GandalfGrey,

also thank you very much  for your help, I will cover your proposal for the next time.

CH

GandalfGray schrieb:

Hi again

There is more than one way to do it but

what I normally prefer to do is stuff like:

Sum( {$<dim1={whatever}, dim2=, dim3=>}  amount)

That is I use the current selection, but sets dim1 to another value of my choice, and unsets any choices in dim2 and dim3

(You often need to use this technique when dealing with year, month, date etc which depends on eachother)