Reset RunningSum within CrossTab based on a condition

Hi,

I need your advice to get the right solution so to achieve the following:
The first table in below pic is a crosstab which shows Count of Ticket ID per month (=Count([TicketID]))

My question is how can I set a condition, so if the previous month result is <15 to add it to the next one? This means if the SUM of a consecutive number of months is below 15 also to be transferred/added to the next month result?

I’ve tested different solutions with combining functions like Previous/Previous(Self), RunningSum, RunningCount etc. etc., but could not get what is needed.

In the second table I’ve manually shown what is the needed result referring the count from first pic. Probably this should be kind of resetting the RunningSum based on a condition (<15) if possible, but I’m not aware how to do it.

Will appreciate any advice given!

WELCOME to BOBJ!!!
Sorry for the mess, but a technical writer I am not.
I did the grey section first
Then Yellow
To come up with the Green
Orange is the base.
In my sample 65 is the number (Trigger) as opposed to your 15 in the sample…it is what my data had to use.

Let me know if this helps. If you have further questions DM me and we can hash out the particulars in your situation. If you expect more than three months not to total 15, then we would need to continue steps grey and yellow until we meet your particular situation expectation.

Sorry for the ‘Forced Solution’, I’m not always the most logical brained individual, but I usually ‘getrdone’

2 Likes

Hello Bo_Bozo,

Thanks for your reply and all the details included in it! Appreciate it!

To answer your question- Yes, I do expect more than three months to have a total below the trigger 15. On a theory it could be even more than an year. So to implement your solution I have to add 12 “grey” variables plus 12 “yellow” variables and to update the “green” variable so to incorporate all the “grey” and “yellow” once.
At the end that should work but I do believe that with such a mighty tool there must be more easier way to do that task.
I’d try your suggestion but still hope someone will provide something that will require lower nb of variables to be used for that purpose.

Hello again Bo_Bozo,
Once again - thanks for your solution and all details shared!
I’ve just implemented it using 12 variables so to cover entire year. However I’ve realized your solution is not exactly what I need as my task is to check if the trigger (in your example 65) is reached in the “Conditional Sum”.
In other words the third row should be 97 not 220 as the previous month result 123 is > 65.
So I’ve changed the “grey” variables (except the first one) NOT to refer the Previous Row(s) results but the “yellow” variables results instead.
However it does not provide correct output yet.
In your solution how will you change it so in the third row the become 97?