Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

macaulay
Contributor

Restrict Dimension by max(Date)

Dear all,

I have Excel-data, that looks like this:

ProjektRechnungsdatumName
101.01.20181. Rechnung
102.01.20182. Rechnung
108.01.20183. Rechnung
231.01.20181. Rechnung

The result (just a usual table in QLIK) shall look like this:

ProjektRechnungsdatumName
108.01.20183. Rechnung
231.01.20181. Rechnung

So I just want the latest data to be shown outgoing from the date of invoice (Rechnungsdatum). I thought about creating a dimension for "Name", but as I am missing a command as "for" I don't know how to do it. Would be great if you can help.

Greetings,

Tom Müller

Tags (5)
1 Solution

Accepted Solutions
MVP
MVP

Re: Restrict Dimension by max(Date)

Try adding a DISTINCT to see if that works

FirstSortedValue(DISTINCT Name, -Rechnungsdatum)

5 Replies
dverbeke
Contributor III

Re: Restrict Dimension by max(Date)

there are several ways to do this, the easiest is to precalculate with firstsortedvalue.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/AggregationFunction...

Remember: everything you do in the front-end is calculated at runtime. It is therefore advised to do the calculation in the Qlik script.

agigliotti
Honored Contributor II

Re: Restrict Dimension by max(Date)

maybe this:

t1:

load

*

from your_table;

left join

load

projekt,

max(Rechunungsdatum)  as your_last_date

resident t1 group by projekt;

t2:

load

*

where your_flag = 1;

load

*,

if( Rechunungsdatum = your_last_date, 1, 0 ) as your_flag

resident t1;

drop table t1;

MVP
MVP

Re: Restrict Dimension by max(Date)

May be try this

Dimension

Projekt

Expression

Max(Rechnungsdatum)

FirstSortedValue(Name, -Rechnungsdatum)


Capture.PNG

macaulay
Contributor

Re: Restrict Dimension by max(Date)

Thanks for the help, when I use FirstSortedValue this is the result.

sorted.JPG

I would really like to use it as a dimension, as I will leave my employer very soon and I think the further users will understand a dimension better than changes in the script. The loading time shouldn't be a problem.

MVP
MVP

Re: Restrict Dimension by max(Date)

Try adding a DISTINCT to see if that works

FirstSortedValue(DISTINCT Name, -Rechnungsdatum)