L
Legacy 462240
Guest
Morning,
Long time lurker, first time poster.
So I have some new business data that concatenates the account number and product code for a unique field that pivots the sales into the month it occurs:
Each month, the pivot is refreshed, the new sales period is added and the numbers refresh. There is 3 years of sales data which can sometimes be a positive or negative number. New business is the first instance of a concat with an integer for the current month OR if the product hasn't been bought for the last 2 years (rolling). Formula for that is sorted: =IF(SUM(G2:AD2)>=0.01,"FALSE","TRUE") for those playing at home.
For the rows that calculate as FALSE, I need to see if the business is 'existing new' or 'current'. Yes, the terminology is ridiculous.
Existing new = after the first occurrence, the business stays new for 12 months.
Current = 12 months after the first occurrence
So, for the first line in the table (102365FE135785), 12 months is counted from period 1806 so all sales are new until 1906 when the business changes to 'current'.
The only time the status would change from current is if there were no sales for 2 years then the first instance would become 'new' business again.
So, for the last line in the table (105846FE561325), the business is 'new' as the first instance was 1810.
As Excel works to the left, I have found the first occurrence in the row with the formula: =INDEX(D2:AH2,MATCH(TRUE,INDEX((D2:AH2<>0),0),0)) but I now need to count 12 months from the first occurrence. I've tried to use offset in various ways and tried to use the time periods as well but nothing seems to work.
Hopefully I have explained properly, let me know if anything needs further explaining.
Thanks,
Noobec
Long time lurker, first time poster.
So I have some new business data that concatenates the account number and product code for a unique field that pivots the sales into the month it occurs:
| Concat | 1805 | 1806 | 1807 | 1808 | 1809 | 1810 | 1811 | 1812 | 1901 | 1902 | 1903 | 1904 | 1905 | 1906 | 1907 | 1908 | IDEAL |
| 102365FE135785 | £10685 | £10685 | £10685 | £10685 | £10685 | £5065 | Current | ||||||||||
| 102365FN658456 | £100 | £100 | £100 | £80 | £85 | Current | |||||||||||
| 106875IN567012 | £168513 | New | |||||||||||||||
| 105846FE561325 | £5560 | £5067 | £5067 | New |
Each month, the pivot is refreshed, the new sales period is added and the numbers refresh. There is 3 years of sales data which can sometimes be a positive or negative number. New business is the first instance of a concat with an integer for the current month OR if the product hasn't been bought for the last 2 years (rolling). Formula for that is sorted: =IF(SUM(G2:AD2)>=0.01,"FALSE","TRUE") for those playing at home.
For the rows that calculate as FALSE, I need to see if the business is 'existing new' or 'current'. Yes, the terminology is ridiculous.
Existing new = after the first occurrence, the business stays new for 12 months.
Current = 12 months after the first occurrence
So, for the first line in the table (102365FE135785), 12 months is counted from period 1806 so all sales are new until 1906 when the business changes to 'current'.
The only time the status would change from current is if there were no sales for 2 years then the first instance would become 'new' business again.
So, for the last line in the table (105846FE561325), the business is 'new' as the first instance was 1810.
As Excel works to the left, I have found the first occurrence in the row with the formula: =INDEX(D2:AH2,MATCH(TRUE,INDEX((D2:AH2<>0),0),0)) but I now need to count 12 months from the first occurrence. I've tried to use offset in various ways and tried to use the time periods as well but nothing seems to work.
Hopefully I have explained properly, let me know if anything needs further explaining.
Thanks,
Noobec
