1

I'm working on WebI 4.2 Support Pack 4 Compilation : 14.2.4.2410. I have an array with the number of days to make an action. For example 967 times an action was make in 0 day.

array of number of day per action

I only want to display lines with 0,1,2,3,7 (one week),14 (two week),21,30 (one month),90 (three month) and more than 90 days cause it will be more relevant for my report. I tried to make a Running Sum on my number of days to get my total of action and then a percentage but it failed.

array of number of day with the running sum

My formula only takes values of the displayed days skipping others. I use this formula to get my interesting days :
=[number_day] Where([number_day]In(0;1;2;3;7;14;21;30;90))

I don't understand why I have an empty frame after '90', i tried to insert the max value of "Number of day" after but that failed too.

Finally how can I get =0+1+2+3+4+5+6+7 in front of '7' instead of =0+1+2+3+7 here is the formula i used : =RunningSum([total_action])

Skyshufeu
  • 62
  • 1
  • 10

1 Answers1

2

I do not quite understand why you want to do this in the manner you have stated. Or maybe I am just not understanding it correctly.

It seems to me you need to create a variable to group your day numbers. Something like this...

DayNumberGroup=If([number_day] InList(0; 1; 2; 3; 7); "One Week"; 
               If([number_day] InList(14); "Two Week";
               If([number_day] InList(21; 30); "One Month";
               If([number_day] InList(90); "Three Month";
               "Three Month+"))))

Then your running sum variable should look like this...

DayRunningSum=RunningSum([Action Sum]; ([DayNumberGroup]))

Having DayNumberGroup as the second parameter for the RunningSum() function causes the sum to restart on a change in the value of DayNumberGroup.

I am not sure why you are excluding the values of 5, 6, 8, etc., but in order for them to not be lumped into "Three Month+" you are going to have to add a filter to your table to exclude them.

Hope this gets you on the right track.

Isaac
  • 3,240
  • 2
  • 24
  • 31
  • 1
    Thank you very much it works perfectly ! I was a bit lost in my way of thinking. I just don't want to display every values that's why I don't use 4,5,6 but i added them in my filter to get the good sum. I'm new here but obviously I should have created an example table to show what I expected. – Skyshufeu Jan 14 '20 at 08:37