Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Ruslans,
Can you please share more realistic data ? That wuld be more helpful to understand.
Br,
KC
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..
May be something like this:
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
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
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)
Try this Ruslans,
Hope you might get some ideas
Br,
KC
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.
You can simply put a filter like this:
RangeCount(above(count({<TranType={'1'}>}Sale),0,RowNo()))
Br,
KC