Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have one table with basicaly 3 columns:
C | DateField | B |
---|---|---|
1 | 1/5/2012 | 105 |
1 | 1/6/2012 | 123 |
1 | 2/3/2012 | 134 |
1 | 2/7/2012 | 122 |
1 | 2/24/2012 | 154 |
1 | 3/4/2012 | 155 |
1 | 4/13/2012 | 213 |
1 | 4/23/2012 | 178 |
1 | 5/4/2012 | 189 |
1 | 5/23/2012 | 187 |
1 | 7/7/2012 | 123 |
2 | 1/1/2012 | 100 |
2 | 1/3/2012 | 110 |
2 | 1/4/2012 | 103 |
2 | 1/5/2012 | 105 |
2 | 3/23/2012 | 145 |
2 | 4/23/2012 | 178 |
2 | 6/6/2012 | 11 |
3 | 1/1/2012 | 100 |
3 | 1/2/2012 | 101 |
3 | 1/6/2012 | 888 |
3 | 2/24/2012 | 154 |
3 | 3/23/2012 | 145 |
3 | 4/12/2012 | 134 |
3 | 4/13/2012 | 213 |
3 | 5/4/2012 | 189 |
4 | 1/1/2012 | 100 |
4 | 1/2/2012 | 101 |
4 | 1/3/2012 | 110 |
4 | 1/4/2012 | 103 |
4 | 2/9/2012 | 122 |
4 | 2/9/2012 | 165 |
4 | 3/4/2012 | 155 |
4 | 4/13/2012 | 213 |
4 | 5/4/2012 | 189 |
4 | 5/23/2012 | 187 |
4 | 7/7/2012 | 123 |
5 | 1/2/2012 | 101 |
5 | 1/3/2012 | 110 |
5 | 1/4/2012 | 103 |
5 | 2/3/2012 | 134 |
5 | 2/7/2012 | 165 |
5 | 2/24/2012 | 154 |
5 | 3/4/2012 | 155 |
5 | 3/23/2012 | 145 |
5 | 4/12/2012 | 134 |
5 | 4/23/2012 | 178 |
5 | 6/6/2012 | 11 |
I want a chart that shows for every C, the first DateField for the max B.
I cannot do that in the load script because the Max depends on my selections. So, let's say I select days 7 to 13. This is the records I get:
C | DateField | B |
---|---|---|
1 | 2/7/2012 | 122 |
1 | 4/13/2012 | 213 |
1 | 7/7/2012 | 123 |
3 | 4/12/2012 | 134 |
3 | 4/13/2012 | 213 |
4 | 2/9/2012 | 122 |
4 | 2/9/2012 | 165 |
4 | 4/13/2012 | 213 |
4 | 7/7/2012 | 123 |
5 | 2/7/2012 | 165 |
5 | 4/12/2012 | 134 |
For that selection, I expect a chart like this:
C | DateField | B |
---|---|---|
1 | 4/13/2012 | 213 |
3 | 4/13/2012 | 213 |
4 | 4/13/2012 | 213 |
5 | 2/7/2012 | 165 |
In the case the Max of column B returns more than one row for the same column C, the Datefield can be the 1st one.
How can I do that?
I am attaching a QVW where I have this data loaded.
Thanks in advance.
Hi,
try using a chart with dimension C, and as expressions:
=max(B)
and
=Date(min(aggr(if(B=max(total<C> B),DateField),C,B,DateField)))
Hope this helps,
Stefan
edit:
If you are not facing multiple Dates for the same max, you could also use
=Date(FirstSortedValue(DateField,-B))
Hi,
try using a chart with dimension C, and as expressions:
=max(B)
and
=Date(min(aggr(if(B=max(total<C> B),DateField),C,B,DateField)))
Hope this helps,
Stefan
edit:
If you are not facing multiple Dates for the same max, you could also use
=Date(FirstSortedValue(DateField,-B))
Hi,
enclose my suggestion.
If(Aggr(NODISTINCT Max(Zahl),Key)=Zahl,Aggr(NODISTINCT Max(Zahl),Key))
regards
Mario
Swuehl,
That worked. Thank you very much!