topic Re: Set analysis with variable modifier in QlikView

Set analysis with variable modifier

Anonymous — Tue, 02 Sep 2014 13:44:43 GMT

Hi all,

I need you help in trying to find an expression that allows me to calculate the revenues for last month. I was thinking to use set analysis to do this, and it actually work when I put '08' in the formula.

=sum({<MONTH = {08}>} Revenues)

The problem is that '08' needs to be a variable according to todays date; it should look something like this:

=sum({<MONTH = {(monthname(today(),-1)}>} Revenues)

But it isn't working 😞

Can somebody help??

Re: Set analysis with variable modifier

JonnyPoole — Tue, 02 Sep 2014 13:47:28 GMT

If you put ' monthname(today(),-1))' in a text box does it return 08 as well ? That is key. Once you get the right expression you can use syntax like the following in the SET STATEMENT:

sum({<MONTH = {' $(= monthname(today(),-1)) '}>} Revenues)

Re: Set analysis with variable modifier

MK_QSL — Tue, 02 Sep 2014 13:53:05 GMT

Try this.....

if MONTH is in MMM format.... i.e. Jan, Feb, Mar etc...

=SUM({<MONTH = {"$(=SubField('$(MonthNames)',';',Month(Today())-1))"}>}Revenues)

or if MONTH is in Numeric Format... i.e. 1, 2, 3 etc

SUM({<MONTH = {"$(=Month(Today())-1)"}>}Revenues)

Re: Set analysis with variable modifier

stigchel — Tue, 02 Sep 2014 14:08:55 GMT

First of all, your syntax is missing the dollar sign expansion syntax (see help), you need to add $(= and a closing bracket.

Then you need to get the format of the equation equal, compare numbers with numbers or text with text. If Month is a text or a date field it will expect text on the right hand. In that case you will need to add quotation marks around, so '$(=....)'

Now you make sure the text is in the same format as the month field, if it's a date use the date function to format e.g.

sum({<MONTH= {'$(=Date(monthname(today(),-1),'MMM-YY'))'}>} Revenues)

you will need to adjust the 'MMM-YY' to your own date format