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: 
saialkesh
Contributor II
Contributor II

Set expression with a variable instead of field

Hi

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

Labels (3)
17 Replies
Saravanan_Desingh

What is the format of [Month & Year]?

What you have defined with the variable CashBalancesAmountYTDCurrent?

saialkesh
Contributor II
Contributor II
Author

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

Michael_Tarallo
Employee
Employee

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

SUM({$<CategoryName={'Sportwear'}>}[$(vMeasure)])

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

Regards,

Mike

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

Regards,
Mike Tarallo
Qlik
saialkesh
Contributor II
Contributor II
Author

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

Sai.

OmarBenSalem

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

sunny_talwar

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.

saialkesh
Contributor II
Contributor II
Author

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

sai.

sunny_talwar

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

saialkesh
Contributor II
Contributor II
Author

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.