Solved

Filter non-dimension columns


Userlevel 2
Badge +1

How can I filter ‘Amount’ column, which is a number formatted column, so that it takes only negative numbers?

GL.Amount[by sum:GL.Month,GL.Account, GL.'Department Pigment'][Filter : Account.'Account Code'="11010"]

In the above formula, I am summing up transaction amounts from “GL” Transaction List  by Month, Account, and Department (which are all dimensions). However, I also want Pigment to take only negative amounts. I tried to paste [GL.Amount < 0] within the filter, but it keeps telling me that the system cannot do that, as GL.Amount is not a dimension but number.

icon

Best answer by francois 7 June 2022, 10:16

View original

3 replies

Userlevel 4
Badge +3

Hi Dastan,

 

When you do [by:] you effectively remove the source dimension (GL) by aggregating, that means you cannot [Filter] on it anymore.

 

Try doing the Filter before the BY, in its own [ ] and you'll see that it works.

Userlevel 4
Badge +3

Nathan is right, once you’ve done the BY, the result is no longer structured by the GL transaction. You’ll have to do the FILTER before, as such:

GL.Amount[FILTER: GL.Amount > 0][by sum:GL.Month,GL.Account, GL.'Department Pigment'][Filter : Account.'Account Code'="11010"]

Userlevel 2
Badge +1

Thank you both! It worked, and will be very helpful in other modelings.

Reply