Skip to main content

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

GL.Amountoby sum:GL.Month,GL.Account, GL.'Department Pigment']nFilter : 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 tGL.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.

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 n ] and you'll see that it works.

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.AmountoFILTER: GL.Amount > 0];by sum:GL.Month,GL.Account, GL.'Department Pigment']nFilter : Account.'Account Code'="11010"]

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

Reply


Ask your question here!

Terms & ConditionsCookie settings