# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
Not applicable

## Sum last 2 values

Hi,

I have a dataset that looks like the following:

IDDateTimeCountryProfit/Loss
122/05/201408:00England-20.00
222/05/201410:00Ireland50.00
322/05/201411:00England10.00
422/05/201415:00England25.00
523/05/201409:00Ireland5.00
623/05/201413:00England-50.00
723/05/201415:00England-15.00
823/05/201416:00England100.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

1 Solution

Accepted Solutions
Partner

Need some script modification to find the last 2 values.

Raw:

Date,

Time,

Country,

[Profit/Loss]

FROM

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

Final:

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])

6 Replies
Not applicable
Author

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?

MVP

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

Not applicable
Author

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?

MVP

Could you post your sample ?

Creator III

Hello Coran,

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

Hope it helps!

Regards,

David

Partner

Need some script modification to find the last 2 values.

Raw:

Date,

Time,

Country,

[Profit/Loss]

FROM

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

Final:

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])