topic Set Expressions Problem in QlikView

Set Expressions Problem

Anonymous — Wed, 15 Feb 2012 11:08:55 GMT

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

Set Expressions Problem

m_woolf — Wed, 15 Feb 2012 11:12:30 GMT

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

Anonymous — Wed, 15 Feb 2012 11:32:34 GMT

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()))

Re: Set Expressions Problem

flipside — Wed, 15 Feb 2012 11:35:42 GMT

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

CELAMBARASAN — Wed, 15 Feb 2012 11:37:10 GMT

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

Anonymous — Wed, 15 Feb 2012 11:46:55 GMT

Wow, thank you all so much!

All of your solutions work perfectly, thank you all .

Kind regards,

Stefan