Forum Discussion
Month value from Runing total
Hello Community, How can I extract month value if I have runing total to each month in year and ID? I know how to do it in Excel: =if(
mid([PERIOD];6;2)=1;[@RUNING_TOTAL];
SUMPRODUCT([RU...
Hi. As a result I would like to get a table:
| ID_PRODUCT | RUNING_TOTAL | ID_REGION | PERIOD | YYYY | MM | TOTAL_MONTH |
| 1 | 4 468 569 | 3 | 2013-01 | 2013 | 1 | 4 468 569 |
| 1 | 13 181 477 | 3 | 2013-02 | 2013 | 2 | 8 712 908 |
| 1 | 18 057 210 | 3 | 2013-03 | 2013 | 3 | 4 875 733 |
| 1 | 23 813 848 | 3 | 2013-04 | 2013 | 4 | 5 756 638 |
| 1 | 31 506 587 | 3 | 2013-05 | 2013 | 5 | 7 692 739 |
| 1 | 37 021 277 | 3 | 2013-06 | 2013 | 6 | 5 514 690 |
| 1 | 40 547 668 | 3 | 2013-07 | 2013 | 7 | 3 526 391 |
| 1 | 48 832 654 | 3 | 2013-08 | 2013 | 8 | 8 284 986 |
| 1 | 52 329 409 | 3 | 2013-09 | 2013 | 9 | 3 496 755 |
| 1 | 55 873 990 | 3 | 2013-10 | 2013 | 10 | 3 544 581 |
| 1 | 64 148 323 | 3 | 2013-11 | 2013 | 11 | 8 274 333 |
| 1 | 70 567 919 | 3 | 2013-12 | 2013 | 12 | 6 419 596 |
| 1 | 78 418 626 | 3 | 2014-12 | 2014 | 12 | 8 429 172 |
| 1 | 69 989 454 | 3 | 2014-11 | 2014 | 11 | 8 807 622 |
| 1 | 61 181 832 | 3 | 2014-10 | 2014 | 10 | 4 549 968 |
| 1 | 56 631 864 | 3 | 2014-09 | 2014 | 9 | 6 542 170 |
| 1 | 50 089 694 | 3 | 2014-08 | 2014 | 8 | 8 348 855 |
| 1 | 41 740 839 | 3 | 2014-07 | 2014 | 7 | 4 847 830 |
| 1 | 36 893 009 | 3 | 2014-06 | 2014 | 6 | 6 176 367 |
| 1 | 30 716 642 | 3 | 2014-05 | 2014 | 5 | 7 485 586 |
| 1 | 23 231 056 | 3 | 2014-04 | 2014 | 4 | 6 057 820 |
| 1 | 17 173 236 | 3 | 2014-03 | 2014 | 3 | 5 035 332 |
| 1 | 12 137 904 | 3 | 2014-02 | 2014 | 2 | 8 699 121 |
| 1 | 3 438 783 | 3 | 2014-01 | 2014 | 1 | 3 438 783 |
And I found that expression above was not final. It should have been:
=IF([@MM]=1;[@[RUNNING TOTAL]];[@[RUNNING TOTAL]]-SUMPRODUCT([RUNNING TOTAL];1*([ID_PRODUCT]=[@[ID_PRODUCT]]);1*([ID_REGION]=[@[ID_REGION]]);1*([YYYY]=[@YYYY]);1*([MM]=([@MM]-1))))
You are welcome.