Set minimum date as only date, when diverse dates exist

[Anonymous]

Dear all together, greetings from windy germany,

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

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

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

[surendraj]

Load Min(date) as Date,Value,Project

from table name

Group by Value,Project;

[devarasu07]

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)

[Anonymous]

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.

[Anonymous]

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

[agigliotti · Accepted solution]

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 ) )

[sunny_talwar]

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

[Anonymous]

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

[agigliotti]

try below:

NewTable:

load

Projekt

Min(Projektstart) as Datum

from Tabelle1$ group by Projekt;

left join (Tabelle1$)

load

Projekt,

Datum

resident NewTable;

[OmarBenSalem]

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;

• Set minimum date as only date, when diverse dates exist