Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data set similar to this:
ID | Date | StdDay | Value |
A | 1/1/2014 | 1 | 100 |
A | 1/2/2014 | 2 | 50 |
A | 1/3/2014 | 3 | 5 |
B | 3/5/2014 | 1 | 200 |
B | 3/6/2014 | 2 | 50 |
C | 4/1/2014 | 1 | 500 |
C | 4/2/2014 | 2 | 400 |
C | 4/3/2014 | 3 | 300 |
C | 4/4/2014 | 4 | 100 |
C | 4/5/2014 | 5 | 40 |
I need a formula that would return the sum of the 'Value' for the maximum 'StdDay' for each 'ID'. The result for this example should be 5+50+40 = 95.
Any ideas?
Thanks
Try:
sum(aggr(firstsortedValue(Value, -StdDay), ID))
Hope this helps!
maybe
=sum(aggr(FirstSortedValue(TOTAL <ID> Value, -StdDay),ID) )
Both answer are ok
Attached the QV