Grouping Dimension values & summing them

[Anonymous]

I am summing the balances of the five digit GL numbers in the below example.  My goal is to somehow group those separate GL numbers and complete the same calculation. 

sum({<GL_NBR={'*90115-*','*90210-*','*90220-*','*90225-*','*90230-*','*90233-*','*90235-*','*90240-*','*90260-*','*90270-*','*90280-*','*90290-*','*90320-*','*90310-*','*90315-*','*90330-*',

'*90340-*','*90370-*','*90380-*','*90390-*','*90395-*','*90405-*','*90410-*','*90415-*','*90419-*','*90420-*','*90425-01-11*','*90426-01-11*','*90425-05-11*','*90426-05-11*','*90251-*',

'*90350-*','*90585-*','*90595-*','*90590-*','*90591-*','*90600-*','*90610-*','*90620-*','*90435-*','*90630-*'}>} "GL_BAL_01")

Those 5 digit numbers represent the GL numbers associated with our operational expenses and I was wondering if there is a way to group them together so I could do calculations with the group, but take up less text in the Expression Editor. I was imagining grouping them as 'Operational' and then doing the same sum calculation.

sum({<GL_NBR={'*Operational*'}>} "GL_BAL_01")  or something that would return the same value as the above calculation.

Thanks!

[Digvijay_Singh]

It is quite possible, all you need to do is to create a new field(GL_Category) while loading GL numbers, that will allow you to use the category in the expression as -

sum({<GL_Category={'Operational'}>} "GL_BAL_01")

[Anonymous]

Thanks for the quick reply,

What would be the syntax for making that new field?

[Digvijay_Singh · Accepted solution]

This has to be in the load script, I assume you have some field from source data indicating which GL_number is operation which is not.

If(Source_field='Operational','Operatioal','Not operation') as GL_Categoty

If you are looking for just front end solution for convenience purpose, I would go for defining variable and use that variable in the expression.