Using a variable in Set Analysis

[mhumphreys]

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.

[juraj_misina]

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

[mhumphreys]

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.

[dplr-rn]

How is vMeasure defined?

make sure it doesnt have an =

[Ivan_Bozov]

How is vMeasure defined?

[mhumphreys]

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.

[dplr-rn]

And you want to use it as

sum( {$} $(vMeasure) )

?

If so vMeasure should just be the column

Thank You

Dilip Ranjith

Sent via mobile

[mhumphreys]

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

[jonathandienst · Accepted solution]

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

[mhumphreys]

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.