Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Set expression with a variable instead of field


Am new to Qlik Sense, and practicing the tool. I need help and will greatly appreciate your support.

I have a pre-defined variable created in the script, which consolidates the total balances in banks. Am creating a new sheet, and in the table, i want to show the Country & the bank balance. The Variable is $(CashBalancesAmountYTDCurrent)

When i drag the country dimension and this variable, everything works fine, however i would want to limit the output to these conditions (and it should not be affected by other selections). Corporate Currency Code should "JPY" and Month & Year should be "May 2020"

 i've tried the below expression, and the output is just '-' (null)

Sum({1<[Corporate Currency Code]={"JPY"},[Month & Year]={"May 2020"}>}$(CashBalancesAmountYTDCurrent))

Appreciate your help

17 Replies
Specialist III
Specialist III

What is the format of [Month & Year]?

What you have defined with the variable CashBalancesAmountYTDCurrent?

Contributor II
Contributor II

Month & Year is Date format. The variable is a lengthy If statement defined by the service provider


Hi Saialkesh - I replied to your DM as well - please see attached example .qvf file - upload to your current environment.


it may be how your set expression is written, for example this works for me - changing the measure dynamically - with the Set Expression


I defined vMeasure in my variables as Sales

The used a variable input box to assign the Variable - either Sales or COS

Let me know how you make out



6-29-2020 2-22-04 PM.jpg6-29-2020 2-21-43 PM.jpg

Mike Tarallo
Contributor II
Contributor II

Hello @Michael_Tarallo 

Thank you so much for responding.

I tried the suggested formula, however i get null values. The variable that am using (CashBalancesAmountYTDCurrent) is a lengthy formula, that also includes set expressions within that.

However, when i use the below formula, i do get to see the value of the total variable in the text field, but it displays '0' in the table-

Sum({1<[Account Type]={'Bank Account'},[Cash Flow Status Code]={'AC'},[Year]={2020},[Month]={'May'},[Bank Description 1]-={'Forecast'}>}$(=$(CashBalancesAmountYTDCurrent)))


Kind Regards



First things first; how is this defined? $(CashBalancesAmountYTDCurrent)

I guess the point to understand here is that you cannot use Sum on top of another sum without using Aggr() function. So for instance

Sum(If(X=0, Sum(Y))) isn't valid...

this is because your inner sum doesn't know what dimensions do you need to evaluate the inner sum on. So you need to provide dimensions for the inner sum using Aggr() function like this

Sum(Aggr(If(X=0, Sum(Y)), Dim1, Dim2, Dim3))

Now coming back to your expression... you can definitely add additional constraints via your outer Sum with Aggr() like this

Sum({1<[Account Type]={'Bank Account'},[Cash Flow Status Code]={'AC'},[Year]={2020},[Month]={'May'},[Bank Description 1]-={'Forecast'}>}Aggr($(=$(CashBalancesAmountYTDCurrent)), Dimension/s))

but to make sure that you are able to ignore selections, you will have to add {1} to your inner expression as well. This is because if inner expressions get filtered out, the outer Sum will not be able to bring them back. In order to ignore selection, both inner and outer will have to ignore dimensions. Filter can be done within inner, outer or both set analysis.

Contributor II
Contributor II

Dear @sunny_talwar 

Thank you so much for your advise.

I tried correcting the formula, but still get the same results (0.00). This is the revised formula:

Sum({1<[Account Type]={'Bank Account'},[Cash Flow Status Code]={'AC'},[Year]={2020},[Month]={'May'},[Bank Description 1]-={'Forecast'}>}Aggr{1<($(=$(CashBalancesAmountYTDCurrent))>},)

The variable that is used here is: CashBalancesAmountYTDCurrent, and this has a lengthy formula, including set analysis in there. The intent is to create a static filter with the above parameters, so that user does not have to change it. The only way, am able to do it now, is through Story.

Appreciate your help

Kind regards


I don't see the usage of dimension in your new expression... you added Aggr(), but not it's dimension....

Contributor II
Contributor II

Dear @sunny_talwar 

Sorry, but can you please guide me on whats missing here. Am not familiar with this. All the values, that i need, i can get from the variable (CashBalancesAmountYTDCurrent). What dimension should i be adding to the syntax

Appreciate your guidance.