get only sum of the value of first minimum date of the maximum year

[jeevays7]

How to get sum(value) from this table for each Dept on first minimum date for the year of 2016.

Ex:

Dept     Date     Value     Year

CSE     28-Dec-2015     50     2015

CSE     1-jan-2016     200     2016

CSE     3-jan-2016     100     2016    

EEE     1-jan-2016     150     2016

EEE     3-jan-2016     100     2016

ECE     5-jan-2016     300     2016

ECE     7-jan-2016     400     2016

Result:

CSE     200

EEE     150

ECE     300

[sunny_talwar · Accepted solution]

Try this:

Dimension:

Dept

Expression:

FirstSortedValue({<Year = {2016}>}Aggr(Sum({<Year = {2016}>}Value), Dept, Date), Aggr(Date, Dept, Date))

[sunny_talwar]

Sample attached

[jeevays7]

Hi Sunny,

Thank you so much. it's working perfect.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

[jeevays7]

Hi Sunny,

Can you explain the part of above Expression,

i.e. Aggr(Date, Dept, Date)

with example or else.

[sunny_talwar]

I don't know if I will be able to explain this well, but It is the second parameter of the FirstSortedValue function which needs to have the same aggregate function as first part of the expression -> Aggr(Sum({<Year = {2016}>}Value), Dept, Date)

[jeevays7]

Thank you sunny,

But, second and third are like that you said.

why you take Date filed at first position in this expression -> Aggr(Date,Dept, Date),

if i take any other field for Date what will happen?

[sunny_talwar]

Date is what we are using to sort. If you want to sort by something else, then you will replace date with that field. The field needs to be a numeric field.

[jeevays7]

Hi Sunny,

Thank you so much for all your reply's.