topic Re: Re: Sum last 2 values in QlikView

Sum last 2 values

Fri, 23 May 2014 15:42:32 GMT

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

Re: Sum last 2 values

Mon, 26 May 2014 08:45:04 GMT

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?

Re: Sum last 2 values

tresB — Mon, 26 May 2014 08:51:42 GMT

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

Re: Sum last 2 values

Mon, 26 May 2014 11:19:01 GMT

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?

Re: Sum last 2 values

tresB — Mon, 26 May 2014 11:52:37 GMT

Could you post your sample ?

Re: Sum last 2 values

daveamz — Mon, 26 May 2014 12:20:11 GMT

Hello Coran,

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

Hope it helps!

Regards,

David

Re: Re: Sum last 2 values

CELAMBARASAN — Mon, 26 May 2014 12:41:38 GMT

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