Issue on FieldValue

[#1]

Hi all,

I've got some trouble to build a simple chart.

Here the data I have :

ID	Client	Product	NumMouvement	Stock value
1	C1	P1	10001	10
2	C1	P1	10002	8
3	C1	P1	10003	15
4	C1	P2	10004	2
5	C2	P1	5002	8
6	C2	P1	5004	10

It's quite simple, it represents all the mouvement I have on my stock for each client and product.

I want to have for each Client and product my final stock.

Something like that :

Client	Product	Final  stock
C1	P1	15
C1	P2	2
C2	P1	10

I first tried to build my chart like that :

Client	Product	Max(NumMouvement)	only( {...}  StockFinal)
C1	P1	10003	-
C1	P2	10004	2
C2	P1	5004	-

Client and Product are dimensions

The two last columns are expressions. The last one use a set analysis : {$<NumMvt={$(max(NumMvt))}>}

You can see the problem, the max in the set analysis is interpreted by 10004 so it doesn't work for the first and last lines of the chart.

Then I tried to use the FieldValue and FieldIndex functions. With FieldIndex I get the number of the line in my data, the I use this number to get the stock value  corresponding to this line. I war really happy with this solution until I see that the number of the line depends on the field with a distinct filter.

For example, if I take my first chart and I try to do a FieldValue('NumMouvement',6) I will have 5004. That is correct.

But if I try to do a FieldValue('stock value',6), I will have nothing because there is less than 6 distinct value in this field.

I hope I was clear on my problem and hope someone can help me on that.

Regards,

Guillaume

[#2 · Accepted solution]

you can try the next expresion: firstsortedvalue([Stock value], -NumMvt). This return the numbers you need but i dont know if in more complex calculation will work.

[#3]

Can be a good way to solve my problem.

I just need to have a LastSortedValue car FirstSortedValue returns the oldest mouvement in my stock I want the last one.

[#4]

I didn't see the - before NumMvt.

It seems to work on simple calculation, I'll test it on something more complex.

Thanks