Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead 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
CELAMBARASAN
Partner
Partner

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

View solution in original post

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?

tresesco
MVP
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?

tresesco
MVP
MVP

Could you post your sample ?

daveamz01
Creator III
Creator III

Hello Coran,

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

Hope it helps!

Regards,

David

CELAMBARASAN
Partner
Partner

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