Solved

Filter non-dimension columns

D
Rank
Userlevel 3
Badge +8

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

Nathan
Rank
Userlevel 6
Badge +11

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.

francois
Rank
Userlevel 6
Badge +14

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"]

François (Pigment)
D
Rank
Userlevel 3
Badge +8

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

Reply


Ask your question here!

Terms & ConditionsCookie settings