Sum last 2 values

[#1]

Hi,

I have a dataset that looks like the following:

ID	Date	Time	Country	Profit/Loss
1	22/05/2014	08:00	England	-20.00
2	22/05/2014	10:00	Ireland	50.00
3	22/05/2014	11:00	England	10.00
4	22/05/2014	15:00	England	25.00
5	23/05/2014	09:00	Ireland	5.00
6	23/05/2014	13:00	England	-50.00
7	23/05/2014	15:00	England	-15.00
8	23/05/2014	16:00	England	100.00

Basically I need to calculate the overall profit/loss for the last two time periods for each date by Country e.g. the result for England would be:

22/05:
10.00+25.00 (only last 2 values)

23/05:

-15.00+100.00 (only last 2 values)

= 120.00

In a straight table I have added 'Country' as my dimension but I need the code for the expression to calculate the total profit for each country.  Any ideas?

Thanks

[#2]

I have added the expression:

Aggr(Sum([Profit]),Date,Country)

This equates to the full profit/loss total for each date by Country.  How can I get the total for only the last 2 rows for each date?

e.g. 22/05 - I have 10 rows but I only want the total profit for the last 2 rows?

[tresesco]

Try: =Aggr(RangeSum(Above(TOTAL Sum([Profit]),0,2)),Date,Country)

[#4]

Thanks tresesco but still now working for me

I am using Date and Country as my Dimensions.

I have the following expression:

Aggr(RangeSum(Above(TOTAL Sum(Profit,0,2)),Date,Country).  If I remove the TOTAL from this expression I get the total for all countries by Date.  However I require the total for the last 2 rows grouped by Date/Country.

Any other ideas?

[tresesco]

Could you post your sample ?

[daveamz]

Hello Coran,

I used a combination of Order By and AutoNumber(). See the file attached.

Hope it helps!

Regards,

David

[CELAMBARASAN · Accepted solution]

Hope the attached will help you

Need some script modification to find the last 2 values.

Raw:

LOAD ID,

     Date,

     Time,

     Country,

     [Profit/Loss]

FROM

[http://community.qlik.com/thread/119621]

(html, codepage is 1252, embedded labels, table is @1);

Final:

LOAD

  ID,

    Date,

    Time,

    Country,

    [Profit/Loss],

  AutoNumber(Time, Date & Country) AS TimeperiodNumber

Resident Raw

Order by Date, Time Desc;

DROP Table Raw;

Expression: Sum({<TimeperiodNumber={1,2}>}[Profit/Loss])