Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dia2021
Creator
Creator

Sum of grouping of fields in a column

Hi All,

I am trying to calculate the sum of groups of specific fields in a column. Also, I need to add capture that group name in a dropdown as well.

I have this load script running 

Load categories, date , status, sales from salesdata

where Categories = 'Paper' or Categories = 'Glass' 

Firstly, I want to capture sum of all highlighted fields 'Shoes', 'Pants', 'Socks', 'Shirts', 'Gloves', 'Sweatshirts' to be grouped together to name as clothes.

Second, I also have a Categories dropdown on the dashboard and I want "CLOTHES" to be reflected in the dropdown to filter the data. Please see attached files for the sample data

How can I achieve this? Any help is greatly appreciated!

 

Labels (2)
7 Replies
Or
MVP
MVP

Seems like a simple enough nested if() or match(), e.g.

if(Match(Categories,'Shoes','Pants','Socks','Shirts','Gloves','Sweatshirts'),'Clothes',Categories) as Categories_Grouped

Of course, this won't return any values in your load script since all of those are excluded by the Where condition requiring Categories = Paper or Glass.

dia2021
Creator
Creator
Author

@Or I already tried this with nested if() and match() fxn.

My problem is I want it to group it to return the sum.  Any idea??

Or
MVP
MVP

It should group and sum automatically if you use the Categories_Grouped field instead of the original Category field..?

dia2021
Creator
Creator
Author

Thank you for the clarification and help! 

How can I add this derived category in the where clause with others? Is that possible?

 

Or
MVP
MVP

You can't use this category in the WHERE clause - you'll have to spell them out individually. You could use a preceding load if you want to refer to the calculation, though, e.g.

Load *
WHERE B = 2;
Load A, A*2 as B INLINE [
A
1,
2];

dia2021
Creator
Creator
Author

Would you please elaborate more on how I can do the preceding load? I have data loaded from the database and where can I add this script

Lib connect to '..........';

Load

Sales_Categories as Categories, date as Date, status as Status, sales as Sales;

[salesdata]:

Select 

Sales_Categories , date , status , sales

from salesdata  where Sales_Categories= 'Paper' or Sales_Categories= 'Glass' ;

** Adding this derived field in the where clause will solve my problem.

I'm attaching a spreadsheet with more clarity to explain what changes I want in a table with this derived field. This is just an example with mock dataset.

Please let me know if you need more clarity.

 

dia2021
Creator
Creator
Author

@Or Can you help me with this one? It's an urgent requirement. How and where can I add the script you have suggested?