Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
joshabbott
Creator III
Creator III

Doing sum(Total <Group> Amount) on a group

Hello!

I have a cyclic group (Group1) set up in my application with 5 field values:

Region

Section

State

Year

Business Area

I also have a straight table set up where the first dimension is this group, and the second dimension is Month.  I have an expression that is sum(amount)

I want to add a field that will tell me what percent each month is of the dimension the user chose in the group.

I should be able to use something like sum(Amount) / sum(Total <Group1> Amount), but I'm not exactly sure how to get the selected value for Group1 into my expression.  I tried sum(Amount) / sum(Total <GetFieldSelections(Group1)> Amount), but this doesn't work either.  Does anyone have any idea's of how I might do this?  Thank you!

1 Solution

Accepted Solutions
Not applicable

If your Cyclic group name is 'Group1', try this. 

Sum(Amount)/Sum(Total <Group1> Amount)

Untitled.jpg

Sean

View solution in original post

6 Replies
Not applicable

From what you are describing, you should want just Sum(Amount)/Sum(TOTAL Amount) as your expression.  You don't have to specify anything with the Group. 

joshabbott
Creator III
Creator III
Author

Thank you for the answer.  Doing a Sum(Total Amount) will bring back the total for all selections in the application.  I want that part of the expression to only bring back the 'Total' at the field the user selected in the group record:

Example, I want to see the last columns value, not the next to last columns value:

Region Month sum(Amount) sum(Total Amount) sum(Total <Group> Amount)

East Jan 50 300 100

East Feb 30 300 100

East Mar 20 300 100

West Jan 120 300 200

West Feb 40 300 200

West Mar 40 300 200

Not applicable

I am not sure I am clear on what you are looking for.  Try this:

Sum(Amount)/Sum({$<Group= >} TOTAL Amount)

If that isn't what you need, do you have a sample file?

joshabbott
Creator III
Creator III
Author

No, I don't need set analysis.  In the example above, if I didn't have groups, and only had the fields I was showing, this would work perfect:

Region Month sum(Amount) sum(Total Amount) sum(Total <Group> Amount)

East Jan 50 300 100

East Feb 30 300 100

East Mar 20 300 100

West Jan 120 300 200

West Feb 40 300 200

West Mar 40 300 200

sum(Amount)/sum(Total <Region> Amount)

But unfortunately, Region is a field in a group so this doesn't work

Not applicable

If your Cyclic group name is 'Group1', try this. 

Sum(Amount)/Sum(Total <Group1> Amount)

Untitled.jpg

Sean

joshabbott
Creator III
Creator III
Author

Thank you, I must have been typing something wrong before, because now it works perfectly.  Thanks!