Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis with variable modifier

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??

Tx

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

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

View solution in original post

3 Replies
JonnyPoole
Employee
Employee

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)

MK_QSL
MVP
MVP

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)

stigchel
Partner - Master
Partner - Master

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