Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harleen_singh
Creator III
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

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

load  OBject no,

        Date(max(Date_new))  as Date1,

     FirstsortedValue(Value,-Date_new)  as  Value_new

group by OBject no;

load    OBject no,

           Date as Date_new,

          Value

From table1;

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

On the basis of Min date

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

load  OBject no,

        Date(min(Date_min))  as Date2,

     FirstsortedValue(Value,-Date_min)  as  Value_new2

group by OBject no;

load    OBject no,

           Date as Date_min,

          Value

From table1;

View solution in original post

5 Replies
Not applicable

On the basis of max date

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

load  OBject no,

        Date(max(Date_new))  as Date1,

     FirstsortedValue(Value,-Date_new)  as  Value_new

group by OBject no;

load    OBject no,

           Date as Date_new,

          Value

From table1;

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

On the basis of Min date

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

load  OBject no,

        Date(min(Date_min))  as Date2,

     FirstsortedValue(Value,-Date_min)  as  Value_new2

group by OBject no;

load    OBject no,

           Date as Date_min,

          Value

From table1;

harleen_singh
Creator III
Creator III
Author

Vishwaranjan,

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

load  OBject no,

        Date(min(Date_min))  as Date2,

     FirstsortedValue(Value,Date_min)  as  Value_new2

group by OBject no;

thanks

Lavi

er_mohit
Master II
Master II

see the attached file\

hope it helps

Not applicable

yes this is my mistake, sorry.

harleen_singh
Creator III
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?