Set Expressions Problem

[Anonymous]

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?

Thanks in advance!

Kind regards,

Stefan

[m_woolf · Accepted solution]

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

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

[Anonymous]

It is fine to use expression there, but the syntax is:
Period={"$(=<your expression>)"}

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

[flipside]

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

[CELAMBARASAN]

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

[Anonymous]

Wow, thank you all so much!

All of your solutions work perfectly, thank you all .

Kind regards,

Stefan