topic Re: Grouping Dimension values & summing them in App Development

Grouping Dimension values & summing them

Anonymous — Fri, 09 Feb 2018 20:18:42 GMT

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!

Re: Grouping Dimension values & summing them

Digvijay_Singh — Fri, 09 Feb 2018 20:22:56 GMT

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

Re: Grouping Dimension values & summing them

Anonymous — Fri, 09 Feb 2018 20:25:49 GMT

Thanks for the quick reply,

What would be the syntax for making that new field?

Re: Grouping Dimension values & summing them

Digvijay_Singh — Fri, 09 Feb 2018 20:58:28 GMT

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.