Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sstefancies
New Contributor III

Using a calculated variable in set analysis

I am trying to use set analysis to hold in place three field values: gl.Yr, gl.Prd, and the left digit of Gl-code

I tried to use a variable to create a new field that is a concatenation of those three fields:

vYrPrdLeft = [gl.Yr]&[gl.Prd]&left([Gl-code],1)

final expression: fabs(sum( {<$(vYrPrdLeft)={‘201685’}>}    [Debit-amt]-[Credit-amt]))

Note I have made the quotes double quotes and also added an equal sign:

fabs(sum( {<$(vYrPrdLeft)={"=201685"}>}    [Debit-amt]-[Credit-amt]))

This did not work in my set analysis, presumably because calculated variables do not work as field names in set analysis.  I have been unable to find an explanation for this, but that has been my experience.

I have tried to simply use multiple fields in my set analysis but only one is ever accepted:

I have the concatenation ($(vYrPrdLeft)) as a filter on the sheet and when “201685” is selected, the correct total number is returned:

421,892

When I clear the filter and use this as my expression:

fabs(sum( {<[Gl-code]={"5*"}>} {<[gl.Yr]={'2016'}>} {<[gl.Prd]={'8'}>} [Debit-amt]-[Credit-amt]))

It returns this number as my total:

4,978,428

Clearly ignoring the second two set analyses

Alternatively, I tried to create a calculated field in the data load:

[gl.Yr]&[gl.Prd]&left([Gl-code],1) AS “vYrPrdLeft”

This threw an error so I tried to add it as a variable which did not throw an error but was ignored completely.  At the end of my data load, I added:

LET vYrPrdLeft = [gl.Yr]&[gl.Prd]&left([Gl-code],1)

I also tried it without the LET:

vYrPrdLeft = [gl.Yr]&[gl.Prd]&left([Gl-code],1)

These were all ignored and did not become available in the sheet.

How can I use set analysis or a variable or a calculated field to lock down those three fields? I’m open to other ways to do this too.  IF won’t work since it will just be looking to see if something is true, which it won’t be unless those fields are selected.

1 Solution

Accepted Solutions

Re: Using a calculated variable in set analysis

How about this?

fabs(Sum({<[Gl-code]={"5*"}, [gl.Yr]={'2016'}, [gl.Prd]={'8'}>} [Debit-amt]-[Credit-amt]))

2 Replies

Re: Using a calculated variable in set analysis

How about this?

fabs(Sum({<[Gl-code]={"5*"}, [gl.Yr]={'2016'}, [gl.Prd]={'8'}>} [Debit-amt]-[Credit-amt]))

sstefancies
New Contributor III

Re: Using a calculated variable in set analysis

That worked!  To be honest that answers a question I have had for a long time now - how to use multiple fields in a single set analysis - and had a lot of trouble finding an answer.  Thank you so much!