Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Not really sure where to start with what I am trying to accomplish below. Basically I need to have an expression that evaluates the CONTRACTMONTH against the last 2 digits of the YEARMONTH...if equal then total the revenue for that month. The example result I need is at the bottom.
COMPANY | SALESPERSON | REVENUE | CONTRACTMONTH | YEARMONTH |
ABC | SZ | 100 | 01 | 201212 |
ABC | JK | 110 | 01 | 201212 |
ABC | BO | 120 | 01 | 201212 |
ABC | SZ | 200 | 01 | 201301 |
ABC | JK | 210 | 01 | 201301 |
ABC | BO | 220 | 01 | 201301 |
ABC | SZ | 200 | 01 | 201302 |
ABC | JK | 210 | 01 | 201302 |
ABC | BO | 220 | 01 | 201302 |
ABC | SZ | 300 | 01 | 201312 |
ABC | JK | 310 | 01 | 201312 |
ABC | BO | 310 | 01 | 201312 |
ABC | SZ | 400 | 01 | 201401 |
ABC | JK | 410 | 01 | 201401 |
ABC | BO | 420 | 01 | 201401 |
ABC | SZ | 400 | 01 | 201402 |
ABC | JK | 410 | 01 | 201402 |
ABC | BO | 420 | 01 | 201402 |
RESULT | ||||
COMPANY | CONTRACTMONTH | YEARMONTH | REVENUE | |
ABC | 01 | 201301 | 630 | |
ABC | 01 | 201401 | 1230 |
Try sum(if(right(YEARMONTH,2)=CONTRACTMONTH,REVENUE)). See attached example.
Thanks. It is returning values but way more revenue than exists for the specific companies. I need to look at why that is happening.