Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Creator III

## Value at max and minimum date of every month

Hello

i have a fields like this

OBject no ,  Date,  Value

I just want fetch "Value" at maximum and minimum date of every month in the back end

what script should i write?

thanks

lavi

1 Solution

Accepted Solutions
Not applicable

On the basis of max date

**********************************

Date(max(Date_new))  as Date1,

FirstsortedValue(Value,-Date_new)  as  Value_new

group by OBject no;

Date as Date_new,

Value

From table1;

*******************************************

On the basis of Min date

------------------------------------

Date(min(Date_min))  as Date2,

FirstsortedValue(Value,-Date_min)  as  Value_new2

group by OBject no;

Date as Date_min,

Value

From table1;

5 Replies
Not applicable

On the basis of max date

**********************************

Date(max(Date_new))  as Date1,

FirstsortedValue(Value,-Date_new)  as  Value_new

group by OBject no;

Date as Date_new,

Value

From table1;

*******************************************

On the basis of Min date

------------------------------------

Date(min(Date_min))  as Date2,

FirstsortedValue(Value,-Date_min)  as  Value_new2

group by OBject no;

Date as Date_min,

Value

From table1;

Creator III
Author

Vishwaranjan,

your answer is correct except this thing. You should not use - sign  for minimum date results

Date(min(Date_min))  as Date2,

FirstsortedValue(Value,Date_min)  as  Value_new2

group by OBject no;

thanks

Lavi

Master II

see the attached file\

hope it helps

Not applicable

yes this is my mistake, sorry.

Creator III
Author

Vishwaranjan,

What if i have more than 1 value at maximum date?

for example if i have data like this

Object no,      Date,           Value

a,           07-04-2013,           7

f,           07-04-2013,           8

g,           07-04-2013,           9

i,           08-04-2013,             7

j,           08-04-2013,              8

k,           08-04-2013,               9

in this case i want fetch all values(7,8,9) at maximum date 08 04 2013. So the result would be the last three records in above example.

how would i achieve this?

Community Browser