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

Accepted answers

kavinkumar

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,

JaredDolich

@PaulWestonVA

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.

All comments

JaredDolich

@PaulWestonVA

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 .

kavinkumar

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 0

I hope this helps!

Regards,

Kavin,

PaulWestonVA

@JaredDolich and @kavinkumar

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.

kavinkumar

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,

I hope this helps!

Regards,

JaredDolich

@PaulWestonVA

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.

3. Add a SUM formula to your value line item in the dashboard module.

Here are the blueprints and screenshots of the modules.

Data Module (blueprint and grid)

Dashboard Module (blueprint and grid)

AN ALTERNATIVE IDEA - USE DISCO

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.

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:

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.

PaulWestonVA

@JaredDolich Many thanks for a detailed explanation. Please accept my apologies for the delay in sending thanks. This is helpful

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…