Dimension for Max date if

[francisco_cohen]

Hi,

I'm using qlik sense desktop and need to create a dimension to get the max date for each unique project code:

My soure table is:

The dimension I want to create would filter the above table into this new one:

Thank you for your support.

FC.

[sunny_talwar]

Why do you need a dimension? May be use an expression

Max(Date)...

but if you really need a dimension, you can try this

Aggr(Max(Date), [Project Code])

[francisco_cohen]

Thank you for your time and sorry for the silly question, but what's the difference between a dimension and an expression?

and to create an expression how do I do it?

[sunny_talwar]

My bad, I should have used Qlik Sense wording.... Measure and dimensions... when you create a table in Qlik Sense, you will see two options... Dimension and Measure (equivalent of expression in QlikView)...

[francisco_cohen]

it worked fine, but I would like to have it in a filter pane so that I could filter by "ALL" or "LAST"

any idea?

[sunny_talwar]

Create an Island Table in the script like this

LOAD * INLINE [

DIM

ALL

LAST

];

And then this will be your filter....

and then use like this

Dimension

=If(GetFieldSelection(DIM) = 'LAST', Aggr(Max(Date), [Project Code]), Date)

[francisco_cohen]

this is my current script, how do I had your code to it?

FOR EACH Combined IN 'Andrew Clements-AC','Andre Melo-AM','Bruno Sequeira-BS','Duarte Teles-DT','Daniel Verbiest-DV','Fabio Andrade-FA','Gonçalo Neves-GN','Hugo Alves-HA','Miguel Araujo-MA','Marc Bartholomew-MB','Rodrigo Costa-RC','Rodrigo Ferreira-RF','Richard Genney-RG','Ricardo Laurêncio-RL','Tim de Groot-TG','Warwick Abrams-WA'

LET name = SubField('$(Combined)', '-', 1);

LET filename = SubField('$(Combined)', '-', 2);

[Activity report]:

LOAD

    "Project Code",

    "Important Info",

    Data,

    Semaphore

FROM [lib://Fsbox PMO BI/Project Managers\PM Financial Info_$(filename).xlsx]

(ooxml, embedded labels, table is [Activity report]);

next Combined

[sunny_talwar]

May be like this

FOR EACH Combined IN 'Andrew Clements-AC','Andre Melo-AM','Bruno Sequeira-BS','Duarte Teles-DT','Daniel Verbiest-DV','Fabio Andrade-FA','Gonçalo Neves-GN','Hugo Alves-HA','Miguel Araujo-MA','Marc Bartholomew-MB','Rodrigo Costa-RC','Rodrigo Ferreira-RF','Richard Genney-RG','Ricardo Laurêncio-RL','Tim de Groot-TG','Warwick Abrams-WA'

LET name = SubField('$(Combined)', '-', 1);

LET filename = SubField('$(Combined)', '-', 2);

[Activity report]:

LOAD

    "Project Code",

    "Important Info",

    Data,

    Semaphore

FROM [lib://Fsbox PMO BI/Project Managers\PM Financial Info_$(filename).xlsx]

(ooxml, embedded labels, table is [Activity report]);

next Combined

LOAD * INLINE [

DIM

ALL

LAST

];

[francisco_cohen]

Dimension

=If(GetFieldSelection(DIM) = 'LAST', Aggr(Max(Date), [Project Code]), Date

I get this error:

error in expression: GETFIELDSELECTION is not a valid function

[sunny_talwar]

My bad, the function name is GetFieldSelections().... with a s in the end