7 Replies Latest reply on Jan 12, 2017 12:34 PM by Guillermo Vidaurre

# Help Identify Change Over Month

Hi

I am trying to identify which customers having growing profit month over month for Q4 in 2015, how do i write a calculated field which would identify customers who have growing profit month over month (ex. profit was higher in october 2015 than september 2015).

• ###### 1. Re: Help Identify Change Over Month

Create a calculated field with something like:

IF SUM([Profit]) > LOOKUP(SUM([Profit]),-1) THEN "Higher"

ELSEIF SUM([Profit]) = LOOKUP(SUM([Profit]),-1) THEN "Same"

ELSE "Lower" END

With the -1, this can be changed to the number of periods back you want to look.  Added to your Table and right click the field and ensure it is computing Table(Down).

• ###### 3. Re: Help Identify Change Over Month

Hi Brian -

Thank you for the quick response, is there a way to easily the identify the customer with growing profit month over month? Please let me know.

Thanks,

Anna

• ###### 4. Re: Help Identify Change Over Month
1. Create a Flag that checks if the profit is higher, i.e. IF SUM([Profit]) > LOOKUP(SUM([Profit]),-1) THEN "Higher" ELSEIF SUM([Profit]) = LOOKUP(SUM([Profit]),-1) THEN "Same" ELSE "Lower" END called "Is Profit Higher?"
2. Create a Flag that checks if the profit is MoM Increasing, i.e. IF [Increasing Profit Flag] = 1 AND LOOKUP([Increasing Profit Flag],-1) = 1 THEN "Yes" ELSE "No" END called "Is MoM Increase?"

See Results:

• ###### 5. Re: Help Identify Change Over Month

You can play with flags to determine if you want several months or the aggregate...it depends now on how you want to view MoM.

• ###### 6. Re: Help Identify Change Over Month

Hi Anna,

Could you elaborate? Are you looking for more than a quick table calculation? If so what is the difference?

• ###### 7. Re: Help Identify Change Over Month

This seems similar to what I have but I am looking for it to be aggregated. So take all the customers that were retained add them up and divide them by the total customers of the previous year. In the example provided the answer should be 73% for 2014, 79% for 2015 and 87% for 2016. However I had to export to excel in order to figure this out.