Issue on FieldValue

Thu, 17 Nov 2011 11:18:01 GMT

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

Issue on FieldValue

Thu, 17 Nov 2011 12:19:05 GMT

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.

Issue on FieldValue

Thu, 17 Nov 2011 12:52:21 GMT

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.

Issue on FieldValue

Thu, 17 Nov 2011 12:54:42 GMT

I didn't see the - before NumMvt.

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

Thanks

topic Issue on FieldValue in QlikView

Issue on FieldValue

Issue on FieldValue

Issue on FieldValue

Issue on FieldValue