How to get the last value for one column in pivot table

[#1]

hello,

i would like to get in a pivot table the last value for one column, i mean the last value loaded depending on the dimension i see in the pivot table

This is the source table loaded in this order:

site	product	version	qty
CET	A	1	10
LLT	A	1	5
LLT	B	3	7
LLT	B	2	8
PFT	C	4	12
PFT	C	3	9

I would like to get the last value for the column "version" but i don't know how to do

This is what i expect :

Site	Product	Version	Sum(qty)
CET	A	1	10
LLT	A	1	5
LLT	B	2	15
PFT	C	3	21

and when i collapse the product level, i would like to see

Site	Version	Sum(qty)
CET	1	10
LLT	2	20
PFT	3	21

Any ideas ? Thanks

[#2]

Hi,

Your load statement should be as follow.

Load Site,

Product,

Max(Version) as Version,

sum(Qty) as Summary_Qty

From ...................

Group by Site, Product;

If your version is numeric, you can follow the above method. Else you can use FristValue() function to achieve the same.

Hope this may help you.

- Sridhar

[sunil2288]

HI Gilles,

Can you explain...What you xactly want to do .

Regards

Sunil

[#4]

Hi Sridhar,

thank you for your reply. Is it the only way to do it in the load statement ?

i would prefer a solution in the report. the example i gave you is quite simple but in the real life my application is more complex and i have thousand of records with a time dimension. so each time i select in the report the time (year, month, day), i would like the report recalcule the last value of the column.

i don't see how i can deal with the load : meaning i must load lastvalue() for each combination of selection in my report (seems difficult)

please let me know your thought.

thanks. Gilles

[#5 · Accepted solution]

Hello! Gilles,

as per my understandind ....You dont have any field that identify the latest value. if you have any field that identify that, the perticular entry is latest . then you can able to get your require output.

Thank you...

[#6]

ok i finally find the solution to my problem.

i use the function FirstSortedValue(Version, -date) in the chart. the minus gives the lastsorted value. Date is what i have called the time dimension (year, month, day) which is loaded with others datas

thanks