Table - using index match type function to return the column of the first non null value

[orangebloss]

I want to create a new column in my table to calculates the location of the first non null value for that row and return the column header, in Excel I'd normally use the Index match function - is there a function in Qliksense that would do something similar?

In the example below row 2 of the data would return 2023/34

Once that's mastered I'd like to be able to change the filter order to the numerical order of the new column (i.e. first to last)

[marcus_sommer]

I assume that such measures aren't necessary if you load the data per crosstable which transforms the data into a normal data-structure.

[orangebloss]

Hi - I have no idea what that means....

It's currently in a pivot table.

[marcus_sommer]

Ok, it aren't source-data else it's a visual. What do you want to do with the column-header of the first non NULL value within this object?

[orangebloss]

I want it to show in the new column so that it can be sorted in order i.e. 2023/4 would come before 2023/15 in the list. I also want to use the value to custom sort the filter list to make it easier to add selections to the table in order and see the impact of adding those selections in .

The table is displaying the resource required if a project was selected, having them in date order would just make it a bit easier to filter than using the alphabetical sort.

[marcus_sommer]

I suggest to enable a proper sorting already within the data-model by using an appropriate numeric value directly and/or as sorting and/or within a dual() like (I assume it are Year/Weeks):

=dual(year(today()) & '/' & num(week(today()), '00'), year(today())*100+week(today()))

and/or by remaining of string-values to create them in a correct alphanumeric way by adding leading zeros to smaller numbers, for example per: num(Value, '00').

[orangebloss]

The values in the headers I can fix at source, how then do I return this value to sort the table as below?

[marcus_sommer]

They mustn't be mandatory adjusted within the source else an appropriate transform could be done within Qlik. The essential point is to load these data as real dates and doing the same with all derived period-information - and a real date is always a number and numbers will be automatically sorted in the right way.

If I understand you right it isn't just the aim to show the results within a pivot respectively crosstable-structure else also the source is coming in this way. In this case you could just apply the above mentioned crosstable load to transform it into normal structures:

The Crosstable Load - Qlik Community - 1468083

and in a following step you could derive the date from your headers, for example per:

makedate(subfield(FIELD, '/', 1), subfield(FIELD, '/', 2)) as Date

 and this is then associated with a master-calendar which contains all further period-information like your YearWeek in all needed ways (pure number, continuous number, dual() and all kinds of string-representation). More background to this essential matter could you find here:

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

Beside this your screenshot looked like you want to use a gantt-chart and for this the following hints might be helpful:

Recipe for a Gantt chart - Qlik Community - 1469842

How to create a Gantt chart using a Pivot table - Qlik Community - 610365