How to create column gap in function of filter

[Fred12]

Hello, I have to create 3 column,  I use filter to select 2 period and I watch gap between period 1 and period 2.

Here an example : 

On qlik sense I have something like this, I select in filter T1 and T4 and  I have just the sum(value) for my columns,  but I don't know how to have gap between Trimester 1 and Trimester 4 in function of what I select,  for example, if I select Trimester 1 and Trimester 2,  I want gap between Trimester 1 and 2 .....

I tried getfield selection ... but no way to work 😕

Thanks for reading me

[vinieme12]

It's not clear about what you mean by creating a gap

can you post some examples

[vchuprina · Accepted solution]

Hi,

There are two possible solutions

Without the Alternate States

My data set

DATA:
LOAD * Inline[
Column, Value
TRIMSTER1, 200000
TRIMSTER2, 250000
TRIMSTER3, 320000
TRIMSTER4, 400000
]
;    

1. Create a measure for the first selected value

SUM({<Column = {"$(=SubField(GetFieldSelections(Column), ',', 1))"}>}Value)

GetFieldSelections(Column) returns list of selected value and separate them by coma,

SubField(GetFieldSelections(Column), ',', 1) return first value before coma

2. Create measure for the second selected value

 SUM({<Column = {"$(=Trim(SubField(GetFieldSelections(Column), ',', -1)))"}>}Value)

Trim(SubField(GetFieldSelections(Column), ',', -1)) subfield returns first value from right side of selected value list, trim function removes empty spaces.

Created GAP measure

SUM({<Column = {"$(=SubField(GetFieldSelections(Column), ',', 1))"}>}Value) - SUM({<Column = {"$(=Trim(SubField(GetFieldSelections(Column), ',', -1)))"}>}Value)

Result:

It will work fine only if you select two values from Column listbox.

If you select three, Qlik will compare the first and third value, if more than three - the first and last value 

Alternate States

1. Add an alternate state in your application

https://www.quickintelligence.co.uk/alternate-states-in-qlik-sense/

2. Copy Listbox Column and assign a new alternate state to it.

You should have two list boxes with different state

3. Create a table with the following measures:

Measure for the default state, this measure will not react to selections in alternate state Listbox

SUM({<Column = {'$(=concat(Column,chr(39)&','&chr(39)))'}>}Value)

This measure will change value only when you select something in alternate state list box.

In my case, I created an alternate state – ‘New’ for this reason I use [New] in the expression

SUM({<Column = {'$(=concat({[New]}Column,chr(39)&','&chr(39)))'}>}Value)
 

Result:

I use concat function, so it's possible to compare values one-to-one, one-to-many and many-to-many

many to many

one-to-one

Regards,

Vitalii

[Fred12]

Wow I tried it, and it works perfectly !! Thanks you so much !!