5 Replies Latest reply: Feb 15, 2012 6:46 AM by Stefan van Diepen

# Set Expressions Problem

Hello to all,

I am encountering a problem using a Set Expression. Here is what i want:

to show the sum of my Net Sales over:

- Company Code 'NL'

- Year '2012'

- Intern/Extern: 'Extern'

- Period: previous period (Period = Month)

Now as you can see, the first three are constants, and I had no trouble building them in, as follows:

```=sum({1 <CompanyCode={'NL'},[Intern/ Extern]={'Extern'},Year={'2012'}>}NetSales)
```

This one works: no matter what I select, the Net Sales shown is based on these 3.

Next, I want to add the last one. However, no matter what I try, I can't get it to work...

I tried adding a 'today' function, combining it with a 'mid' function, something like this:

```=sum({1 <CompanyCode={'NL'},[Intern/ Extern]={'Extern'},Year={'2012'},Period={mid(today(0),5,1)}>}NetSales)
```

Today(0) gives '15/02/2012', so mid(5,1) gives '2'. However, it gives an error (Error in set modifier ad hoc element list:) and I can't get it right...

Anyone have any idea how I can automatically let QV show the data from the previous month?

Kind regards,

Stefan

• ###### Set Expressions Problem

create a variable to hold the value of mid(today(0),5,1)

Then use the variable in your set expression   \$(vMyVariable)

• ###### Set Expressions Problem

It is fine to use expression there, but the syntax is:

And the easier way to get month number from date is num(month(today()))

• ###### Re: Set Expressions Problem

Hi Stefan,

Further to mwoolf's solution, you can also just expand the mid(today(0),5,1) value making the second expression as this ...

sum({1 <CompanyCode={'NL'},},[Intern/ Extern]={'Extern'},Year={'2012'},Period={\$(=mid(today(0),5,1))}>}NetSales)

.. all depends on whether you prefer clearer expressions, or fewer variables!

flipside

EDIT: Michael beat me to it!!

Message was edited by: flipside

• ###### Set Expressions Problem

Hi,

You need do use withing dollar expansion \$(=).

`=sum({1<CompanyCode={'NL'},[Intern/Extern]={'Extern'},Year={'2012'},Period={\$(=mid(today(0),5,1))}>}NetSales)`

Hope it helps

Celambarasan

• ###### Set Expressions Problem

Wow, thank you all so much!

All of your solutions work perfectly, thank you all .

Kind regards,

Stefan