6 Replies Latest reply on Nov 2, 2016 2:31 PM by Steffen Nielsen

# Use sum of measure in one month to filter customer names

Hi everyone,

I have an issue that I cannot seem to solve.

Lets say I have a list of customers with ratings from 1-4 - I then want to track if anyone of these customers rating is changing during the year.

I have then created a calculated field to track if the change is +1, 0 or -1 (lets assume it can only change once at a time) - this works pretty well.

Problem is my list of customers is very long and I want to filter the customer to only show the ones that have actually made a change.

Basically what I want to do is, to check if the running sum in month 7 is bigger than 0. If it is, I want to have that Customer Name in the list - and show data for it in all the months.

However if I use the running sum as a filter, I on get data in the row from when the change first appears - say the rating goes from 2 to 3 in March, I will only get data in my row from March on onwards - where in reallity I want to show data from the beginning of the year - but filtered to only include the customers which have a running sum in july > 0.

Does that make sense?

• ###### 1. Re: Use sum of measure in one month to filter customer names

Can you post your workbook and explain requirement with proper example? I understood the overall requirement but not clear about what should be your output.

• ###### 2. Re: Use sum of measure in one month to filter customer names

I would like in the 3rd tab to show only the customers which have changed their rating during the year. So the running_sum in July should be > 0.

• ###### 3. Re: Use sum of measure in one month to filter customer names

Hi Steffen,

Please take a look at the attached workbook and let me know if it helps or the solution that you were looking for.

I have created two new calculations as below to get customer with improved ratings.

Rating Migration New

{ FIXED [Customer Name] : MIN(IF [Year Month] = { FIXED : MAX([Year Month]) } THEN [Rating] END) }

-

{ FIXED [Customer Name] : MIN(IF [Year Month] = { FIXED : MIN([Year Month]) } THEN [Rating] END) }

Filter

IF [Rating Migration New]>0 THEN 1 ELSE 0 END

Check the newly created sheet - Rating Migration New for the same.

Thanks,

Nikunj

• ###### 4. Re: Use sum of measure in one month to filter customer names

Hi Nikunj,

Sorry for the late answer. Unfortunately this solution doesn't seeem to take into account if the rating has changed over period - but only if there is a difference between first month and last month. If there has been any changes during the year - I want to capture that customer name as well.

Brgds,

Steffen.

• ###### 5. Re: Use sum of measure in one month to filter customer names

Hi Steffen,

Can you try this formula for Filter?

IF [Rating Migration New]!=0 THEN 1 ELSE 0 END

Put that filter same as before.

Let me know If it helps or not.

Thanks,

Nikunj

• ###### 6. Re: Use sum of measure in one month to filter customer names

Hi Nikunj,

Unfortunately this still doesn't work even with your new input.

However I have developed a solution - it is definitely not the prettiest solution, but it seems to work.

If have created two calculated fields - one that calculates the running sum of the absolute difference in rating. The second field calulates how many columns are in the set and then adds that number to the current column to find the running_sum_field in the last column. If this makes sense - that way I am able to identify all the rows that have had a change in rating (up or down) as well as displaying data for each month in the row.

If interested see attached workbook.

1 of 1 people found this helpful