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

[sunny_talwar]

It is difficult to know what you have in your app by just looking at an expression and that too one which is hiding behind a variable.... but all I am saying is that you need a dimension or a list of dimensions like this

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

, Dim1, Dim2, Dim3))

But again, I have no idea about your data so, knowing what exactly will work is difficult. I can just give you a general direction. For better help, may be provide a sample. 

[saialkesh]

Dear @sunny_talwar 

I tried with the below syntax -

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

and i get null values in the output.

Thanks in advance.

[sunny_talwar]

Expression is not right :)... Again no way for me to know... but the correct syntax should be

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

    $(CashBalancesAmountYTDCurrent)

, Bank))

[saialkesh]

Dear @sunny_talwar 

Apologies, i understand you working in the dark. I tried the correct syntax & the output is still null

I will create a sample file & share, so that it provides you with more insights, to help.

Thanks for everything.

Regards

sai.

[saialkesh]

Dear @sunny_talwar 

I tried with all possible combinations, and it still fails. Am sorry, am not able to create a qvf file, but will try to explain it here:

1. We have many variables that are created by the vendor and one of them is the CashBalancesAmountYTDCurrent

2. This variable has in it, nested If & set analysis functions, which checks for conditions and then totals up the cash balance amount, displayed by applied metrics of country, bank, currency etc.,

3. On the report, that we want created, we want to show CashBalancesAmountYTDCurrent by Banks, Country & Currency in 3 different tables. We can do that, provided we apply the filters - Month, Year, Account Type, Currency etc., and the report works fine, when i include that in Story.

4. We would want to have the report without filters applied externally and achieve that through SetAnalysis.

I appreciate all your support and guidance.

Best,

Sai.

[sunny_talwar]

Hi @saialkesh 

All the information provided above is something we already know. What I don't know is the following

1) Where are you using this expression? What dimensions are you using in the object if any?

2) Is it possible that CashBalancesAmountYTDCurrent contains set analysis which contradicts the set analysis from your outer set analysis. For example, outer set analysis wants to look at only year 2020, does your inner set analysis says year 2019? Or there is no set analysis in the inner part and are you selecting 2019? That was just one example, but you have many other fields you have set analysis on: Cash Flow Status Code, Month, Bank Description 1.

3) Also, outer expression wants to ignore selection in all fields. Does the inner expression does that too?

It is almost impossible to give you an exact expression which will work without knowing all these things in details. All, I can give you is a general direction.

[saialkesh]

Dear @sunny_talwar 

Am trying to create a simple table. The dimensions are :

Country & CashBalancesAmountYTDCurrent

for the above table, i use the expression $(CashBalancesAmountYTDCurrent)

This is the variable expression of CashBalancesAmountYTDCurrent -

in order to refine my table, i want to apply the static filters for Currency=JPY, Account Type=Bank Account, Month & Year = June 2020, Status=AC and bank description not equal to Forecast. 

I hope i've been able to answer your queries.

Thanks for your help

Best

Sai.

[saialkesh]

On the variable expression, please ignore the first line. The variable definition starts from If(Dimensionality()...

Sorry about the error.