SUM for certain time period

[sculptorlv]

I have the following data:

Data               Quantity

01.01.2018     1

04.01.2018     15

06.03.2018     18

17.04.2018     3

How can I get cummulative sum for each month name (until Dec 2018)?

I need to get the following results:

MonthName     Sum

Jan 2018          16

Feb 2018          16

Mar 2018          34

Apr 2018           37

May 2018          37

..

Dec 2018          37

I hope for your help

[sculptorlv]

Range funcions really can help.

Can you please help me with a little bit more diffucutl problem?

I have Date, TransactionNumber and TransactionType for each row.

I need to find the Date (not the Quantity) within previous mentioned time periods where TransactionType =1 and TransactionNumber is MAX (= the last one).

For example, the results might be:

MonthName     OrderDate

May 2014           12.05.2014

Jun 2014              12.05.2014 (thre were no transactions in June, but the last transaction is on 12th of May)

Jul 2014               17.07.2014

[jyothish8807]

Hi Ruslans,

Can you please share more realistic data ? That wuld be more helpful to understand.

Br,

KC

[sculptorlv]

Yes sure..

Transaction type 1 - is device install to client, Transaction type 0 - device is returned to warehouse.

I need to calculate, how many devices are total on clients for each month (at the end).

Thus, I try this formula:

rangecount(above(count(FA_Number),0,RowNo()), FA_Movement_To_Type = 1 AND FA_Movement_Posting_Date = MAX(FA_Movement_Posting_Date) )

But is doen't work correctly..

[jyothish8807]

May be something like this:

[sculptorlv]

Thank you for this information.

Can you please alse tell me, how I can put limitation in equation?

I need to take into account only last reccord for whole period for each device.

Example data:

12.05.2017

15.05.2017

03.07.2017

The result:

for May, the date must be taken in account is 15.05.2017

for June, the date mast be taken in account is 15.05.2017

for July, the date mast be taken in account is 03.07.2017

[jyothish8807]

Hi Ruan,

How are you planning to tackle the below situation ? which max date should be considered ?

Example data:

12.05.2017 --> FA_Movement_To_Type = 1

15.05.2017 --> FA_Movement_To_Type =  0

Br,

KC

[sculptorlv]

I will take the last date 15.05.2017 and will know, that there is NO device on client (because 0 menas the final action was to remove from client)

[jyothish8807]

Try this Ruslans,

Hope you might get some ideas

Br,

KC

[sculptorlv]

But how can I ignore rows with incorrect Transaction type?

For June, the Cum Sum have to be 1. Because, there is no rows with transaction tyep 1 in June.

[jyothish8807]

You can simply put a filter like this:

RangeCount(above(count({<TranType={'1'}>}Sale),0,RowNo()))

Br,

KC