Reset CUME() value when new encounter new key

[alexle]

I'm using CUME() function in my expression to do calculate a running-total. However, the CUME function doesn't reset the value when a new key (program name in my case) is encountered. The CUME() sums all the rows from top to bottom as one single category.

I would like to know if there is a way to group the CUME value by program name.

thank you.

 

[MacLeod72]

I don't believe that CUME() is going to work for this purpose.

How I do this is to use variable ports. Since variable ports are set in port number order, you can save the previous row's data and use it in the current row's calculation. If you use this, you should get a running total broken by key:

Port 1: KEY Input
Port 2: VAL Input
Port 3: V_TOTAL Variable Exp: IIF(KEY != V_KEY, VAL, V_TOTAL + VAL)
Port 4: V_KEY Variable Exp: KEY
Port 5: RUNNING_TOTAL Output Exp: V_TOTAL

I haven't had my coffee, but I think this will work

--Rob

 

[MacLeod72]

I forgot to mention that the data needs to be sorted by KEY going into this transformation.

--Rob

 

[alexle]

Thank you for responding.

do these ports have to be in the exact sequential order as you described?

 

[MacLeod72]

The V_TOTAL port does need to be numerically before the V_KEY port. Other than that, the port order does not matter.

Hope this helps!

--Rob

 

[alexle]

Thank you MacLeod72...Your solution worked great.

Thank you so much...