Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
sdealtrey
New Contributor

Grouping Dimension values & summing them

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!

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Grouping Dimension values & summing them

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.

View solution in original post

3 Replies
Digvijay_Singh
Honored Contributor III

Re: Grouping Dimension values & summing them

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

sdealtrey
New Contributor

Re: Grouping Dimension values & summing them

Thanks for the quick reply,

What would be the syntax for making that new field?

Digvijay_Singh
Honored Contributor III

Re: Grouping Dimension values & summing them

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.

View solution in original post