Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this:
Dimension:
Dept
Expression:
FirstSortedValue({<Year = {2016}>}Aggr(Sum({<Year = {2016}>}Value), Dept, Date), Aggr(Date, Dept, Date))
Try this:
Dimension:
Dept
Expression:
FirstSortedValue({<Year = {2016}>}Aggr(Sum({<Year = {2016}>}Value), Dept, Date), Aggr(Date, Dept, Date))
Sample attached
Hi Sunny,
Thank you so much. it's working perfect.
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 .
Hi Sunny,
Can you explain the part of above Expression,
i.e. Aggr(Date, Dept, Date)
with example or else.
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)
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?
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.
Hi Sunny,
Thank you so much for all your reply's.