SUM in conjunction with boolean filter
I have 2 modules: Data Module and Dashboard module.
1. Data Module has:
Region (List Item) Product (Line Item with List Items called PRODS) Value (Line Item) Export? (Line Item - Boolean)
e.g North America PRODS=Mountain Bike 100 True
Europe PRODS=Mountain Bike 50 False
I would like the Dashboard module to have:-
Region (List Item) PRODS (List Item) Export Value(Line Item) Local Value(Line Item)
North America Mountain Bike 100 0
Europe Mountain Bike 0 50
So I need in the Dashboard module something like these:-
Export Value = 'Data Module'.Value [SUM: 'Data Module'.Product where 'Data Module'.Export?=True]
Local Value = 'Data Module'.Value [SUM: 'Data Module'.Product where 'Data Module'.Export?=False]
How do I use that Boolean as a with SUM?
Many thanks
Best Answers
-
Ah, got it.
Then play with 'All' or 'Any' function based on your request.
All - If all the checkboxes are ticked for the same product then the result will be true.
Any - If any one of the checkboxes is ticked then the result will be true.
Your formula should be,
Export Value = if Data Module'.Export?[ALL:Data Module.Product] then 'Data Module'.Value[SUM: Data Module.Prod] else 0
Local Value = if NOT Data Module'.Export?'[ALL:Data Module.Product] then Data Module'.Value[SUM: Data Module.Prod] else 0
I hope this helps!Regards,
2 -
Ah, okay. I understand better.
I'm going to provide you a possible solve for this and at the end I'm also going to suggest a different way - a little closer to best practices - on how you might want to set up your modules.
Strictly working with the way you have it, here is an idea for you.
1. Remove the Boolean from the data module.
2. Add the Boolean to your dashboard module and the logic provided by @kavinkumar (see the blueprint below to see an example) since this module has the correct dimensionality for the Boolean. I don't see the time dimension so we're still being as efficient as possible. If you do use the time dimension please follow the idea at the end of this post.
3. Add a SUM formula to your value line item in the dashboard module.
4. Now add the IF statements - remember though, it's really important that the IF statement exits as early as possible. So try to figure out which condition is most likely and make that your first condition in the IF statement.
Here are the blueprints and screenshots of the modules.
Data Module (blueprint and grid)
Dashboard Module (blueprint and grid)
AN ALTERNATIVE IDEA - USE DISCO
The one thing I noticed about your data module is that you use the region as a dimension. I don't 100% know your business case here but that will limit how many products you can assign to a region, namely one. If you want to be able to have multiple products for multiple regions, you need to set up your data module with a unique identifer of both dimensions. I have a hunch this is what you're after.
Note: I'm showing you best practice which allows this to scale and to be as efficient as possible. So it may seem like there's a few extra steps but in reality, long term, this is a very supportable way to handle this.
1. Set up your data module to have a concatenated key of both region and product. If you use the time dimension, add that as a dimension. Do not concatenate time to your UID. See @rob_marshall data hub best practices. The data file without time will look like this:
2. Create a new list using this file. Use the UID as "name only" to import into your list. Make sure you UID does not exceed 60 characters.
3. Create your data module using this new list. This is your only dimension. If you use time then add the time dimension as a dimension, not as a line item. Also, if you use time then do not import the region and product. Create another system module with your new list and without time and add them there by parsing the UID. Again, you can read why on @rob_marshall best practice post.
4. Now your data file has the value and the two dimensions you need to do your SUM function. So in your Dashboard module, you can just sum on product and region like so:
And voila! you have multiple products for multiple regions!! You can set your Booleans here since the combination of region and product is what you are after. Depending on the importance of the time dimension you can also set the Booleans in a different system module (if your Boolean is not time dependant) or leave it in the dashboard module (if the Boolean is time dependent).
Good luck @PaulWestonVA !! Please let us know in this chain how it turns out or if you need more details on any of these points. Happy to get you to the right place.
1
Answers
-
This is a good question and there are many good answers.
You didn't show a time dimension so I'm assuming that the data module is as you showed it.
One idea for you is to just use the data module as both data and dashboard since you're using the exact same dimensions. No point really in splitting them up. You can see how to set it up in this example below.
Normally, the data module is for transactions and is flat, meaning it only has one dimension.
If your data file looks like that I would suggest following the DISCO methodology and set up a system module and probably a calculation module to do your transformations from value to export and local.
Here's an article on best practices from @DavidSmith .
1 -
HI @PaulWestonVA,
As @JaredDolich mentioned, please follow the best practises. Coming to your concern, there is no need for using 'SUM' function in between Data and Dashboard modules as they have same dimensions. So your logic should be formulated in this way,
Dashboard Module:
Export Value = if Data Module'.Export? then 'Data Module'.Value else 0
Local Value = if NOT Data Module'.Export?' then Data Module'.Value else 0I hope this helps!
Regards,
Kavin,
2 -
Many thanks for your responses however I have not explained the subtlety of the Products in my query. In the data module the Product has a number format of list item and the list is PRODS
However in the Dashboard module PRODS is the list item dimension
and I need to be able to get the correct answers.
0 -
@JaredDolich Many thanks for a detailed explanation. Please accept my apologies for the delay in sending thanks. This is helpful
0