Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
@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??
It should group and sum automatically if you use the Categories_Grouped field instead of the original Category field..?
Thank you for the clarification and help!
How can I add this derived category in the where clause with others? Is that possible?
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];
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.
@Or Can you help me with this one? It's an urgent requirement. How and where can I add the script you have suggested?