Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

macaulay
Contributor

Set minimum date as only date, when diverse dates exist

Dear all together, greetings from windy germany,

I got a probably simple question. I have an excel sheet, which looks like this:

ProjectDateValue
5001.1.20171000

505

5.6.20175000
506
6.6.2017
2500
508
5.6.2017
3200
500
5.6.2017
200
540
10.6.2017
5400
564
5.6.2017
2000
576
5.6.2017
6000
500
30.6.2017
2000

As it can be seen, Project 500 exists three times. Every time the project is listed up new (newer date), it means that it's value has rised up about the value shown on the right collumn. In my Sense App I have set a filter, that filters the date of the Project start. So f.e. I set a filter for "June 2017". Qlik shows me beneath many other projects also project "500". My problem is, that it should not be shown, as the date benath project "500" just shows the date of when the value has rised up. But the start of the project was on January the first.

I tried to set <min(Date) as Date>, but that doesn't work. How can I adjust my script so that the project is just shown once, when the earliest date concerning the project is set?

Thanks for the replies

Tags (4)
1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: Set minimum date as only date, when diverse dates exist

you can do something as below:

NewTable:

load

Project

Min(Date) as MinDate

from table group by Project

left join (table)

load

Project,

MinDate

resident NewTable;


Chart settings:

dimension: Project

measure: Sum( if( Date = MinDate, Value, 0 ) )

10 Replies
surendraj
Valued Contributor

Re: Set minimum date as only date, when diverse dates exist

Load Min(date) as Date,Value,Project

from table name

Group by Value,Project;

devarasu07
Honored Contributor II

Re: Set minimum date as only date, when diverse dates exist

Hi,

Try this

LOAD *,Date(Date#(Date,'D.M.YYYY'),'DD/MM/YYYY') as NewDate INLINE [

    Project, Date, Value

    500, 1.1.2017, 1000

    505, 5.6.2017, 5000

    506, 6.6.2017, 2500

    508, 5.6.2017, 3200

    500, 5.6.2017, 200

    540, 10.6.2017, 5400

    564, 5.6.2017, 2000

    576, 5.6.2017, 6000

    500, 30.6.2017, 2000

];

Dimension:

Project

Measure 1: Date

min(NewDate)

Measure 2:

sum(Value)

macaulay
Contributor

Re: Set minimum date as only date, when diverse dates exist

My original source contains around 10.000 projects (and 10 more each day), so an inline load will probably not work for that. Yet thanks for the idea.

macaulay
Contributor

Re: Set minimum date as only date, when diverse dates exist

I tried it with min(), but QLIK loads every date and shows the projects for all the dates.

agigliotti
Honored Contributor II

Re: Set minimum date as only date, when diverse dates exist

you can do something as below:

NewTable:

load

Project

Min(Date) as MinDate

from table group by Project

left join (table)

load

Project,

MinDate

resident NewTable;


Chart settings:

dimension: Project

measure: Sum( if( Date = MinDate, Value, 0 ) )

MVP
MVP

Re: Set minimum date as only date, when diverse dates exist

Inline load is just to show how the script following the load needs to look like.... you need to use your own table load when you are doing this for your app

macaulay
Contributor

Re: Set minimum date as only date, when diverse dates exist

Thanks for your help, as I am not very common with scripting I think I did something wrong. Could you check please?

left join.JPG

agigliotti
Honored Contributor II

Re: Set minimum date as only date, when diverse dates exist

try below:

NewTable:

load

Projekt

Min(Projektstart) as Datum

from Tabelle1$ group by Projekt;

left join (Tabelle1$)

load

Projekt,

Datum

resident NewTable;

omarbensalem
Esteemed Contributor

Re: Set minimum date as only date, when diverse dates exist

u can't use aggregation functions in the script 'max, min, sum, count etc) without using group by (and the groupe by MUST be done with ALL the rest of the fields in the table.

That's why Andrea, in his answer above, added the group by clause:

NewTable:

load

Projekt

Min(Projektstart) as Datum

from Tabelle1$ group by Projekt;

left join (Tabelle1$)

load

Projekt,

Datum

resident NewTable;