Set expression with a variable instead of field

[saialkesh]

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

[Saravanan_Desingh]

What is the format of [Month & Year]?

What you have defined with the variable CashBalancesAmountYTDCurrent?

[saialkesh]

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

[Michael_Tarallo]

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

[saialkesh]

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]

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]

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.