9 Replies Latest reply: Nov 2, 2018 1:23 AM by Jonathan Dienst

# Using a variable in Set Analysis

I am using Qlik Sense - June 2018 Version.

I am trying to retrieve the values from a set period of time (in this case quarters).  I am able to do this using this formula:

sum( {\$<MonthNum = {'>0<4'}>} [QTY Shipped LB] )

which works quite well.  However I want to get creative and have been using variables on a straight table so that they mimic the "alternative measures" found in graphing.  I am able to pick a unit of measure (Lbs, \$, Margin) from a variable extension I have simply by clicking on a button.

My problem is I have a variable called "vMeasure", but no matter how I either change the Set Analysis or try and use it in other formulas nothing appears to work.

Any help you can give would be appreciated.  Or a point in the right direction.  I have searched all over but could never find what I need.

• ###### Re: Using a variable in Set Analysis

Hi Michael,

it's not clear what problem exactly are you facing, but I'll post a working example, maybe it helps you undrestant things better.

```Sum({<\$(sIgnoreDateFields), Year={\$(sMaxYear)}, %IDMonth={"<=\$(sMaxMonthID)"}>}[\$(vMeasure)]/if(vMeasure='#Sales',\$(vCurrencyRate),1))

```

Where

sIgnoreDateFields = Year,Quarter,Month,[Year-Month] //to turn off selected time filters

sMaxYear = Max(Year) //to select particular year

sMaxMonthID = Max(%MonthID) //to select particular month(s)

vMeasure is set by a button (#Sales or #Quantity)

vCurrencyRate is set by a button to a selected currency rate.

This expression calculates YTD sum of Sales or Quantity (based on user input via button) for a particular year. If user chooses Sales, it also devides the sum by a user chosen exchange rate .

Hope this helps.

Juraj

PS: your example contains a typo. If you want to make a search in set analysis, you should always use double quotes:

sum( {\$<MonthNum = {">0<4"}>} [QTY Shipped LB] )

• ###### Re: Using a variable in Set Analysis

Thanks for the response Juraj.  I am trying to use a formula similar to this one but use my variable.

sum( {\$<MonthNum = {">0<4"}>} [QTY Shipped LB] )      original formula

sum( {\$<MonthNum = {">0<4"}>} \$(vMeasure) )             formula I wish I could use

When I try the formula with a variable in it I get all null values.  I will try to digest what you wrote.  I am fairly new to this.

• ###### Re: Using a variable in Set Analysis

How is vMeasure defined?

make sure it doesnt have an =

• ###### Re: Using a variable in Set Analysis

How is vMeasure defined?

• ###### Re: Using a variable in Set Analysis

vMeasure is defined as :

Sum(SALES)

There is no "=".

I then use an Extension that gives me the ability to pick alternative measures as well.

• ###### Re: Using a variable in Set Analysis

And you want to use it as

sum( {\$} \$(vMeasure) )

?

If so vMeasure should just be the column

Thank You

Dilip Ranjith

Sent via mobile

• ###### Re: Using a variable in Set Analysis

If vMeasure is Sum(SALES), then that means that you are trying to nest a sum expression inside another. Nested aggregations are not allowed, so this expression is failing.

In essence, you would need something like

sum({\$<MonthNum = {">0<4"}>} Aggr(\$(vMeasure), dim1, dim2))

(where dim1, dim2,...dimN would be the chart dimensions)

But perhaps you should explain why you would want to nest the sums. What you are asking for seems to be a complicated and expensive way of saying

Sum({\$<MonthNum = {">0<4"}>} SALES)

Have you tried setting vMeasure to "SALES"?

• ###### Re: Using a variable in Set Analysis

Jonathan - your formula works perfectly.  The reason I needed it is because I have buttons on the top of the report that determine which unit of measurement the report is shown in.  I do not want it to show always in SALES (dollars), I want the user to determine which type of information he sees.

What you have shown me is exactly what I have been looking for.

Thanks.

• ###### Re: Using a variable in Set Analysis

I spoke too early.  It is almost there, however the selection of the months Jan-Mar is not working, but I think you put me on the right track.

• ###### Re: Using a variable in Set Analysis

Thanks again Jonathan.  I was able to find what I needed thanks to your formula.  Here is the final result.

sum({\$<MonthNum={\$(='1,2,3')}>} Aggr(\$(vMeasure), [Core Business Description],Month  ))

This gives me Quarter 1 sales based on whatever button is pressed (\$, LBS, or Margin).

Thanks to everybody that helped.

• ###### Re: Using a variable in Set Analysis

Glad to help. You can probably simplify the set expression slightly

sum({\$<MonthNum={1,2,3}>} Aggr(\$(vMeasure), [Core Business Description],Month  ))

• ###### Re: Using a variable in Set Analysis

Dilip - anytime I use a variable with a sum(or other aggregate) it never works.  See picture of report.

• ###### Re: Using a variable in Set Analysis

Just try sum(vMeasure)

• ###### Re: Using a variable in Set Analysis

Jeshwanth - doesn't work at all.