Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Dimension for Max date if

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:

Untitled.png

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

Untitled1.png

Thank you for your support.

FC.

12 Replies

Re: Dimension for Max date if

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
New Contributor III

Re: Dimension for Max date if

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?

Re: Dimension for Max date if

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
New Contributor III

Re: Dimension for Max date if

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?

Untitled2.png

Re: Dimension for Max date if

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
New Contributor III

Re: Dimension for Max date if

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

Re: Dimension for Max date if

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
New Contributor III

Re: Dimension for Max date if

Dimension

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


I get this error:

error in expression: GETFIELDSELECTION is not a valid function

Re: Dimension for Max date if

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