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

     

    image.png

     

    image.pngI hope this helps!

     

    Regards,

     

  • @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)

    Sum002.pngSum002B.png

    Dashboard Module (blueprint and grid)

    Sum003A.pngSum003B.png

     

    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:

    Sum010.png

    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.

    Sum011.png

    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.

    Sum012.png

    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:

    Sum013.png

    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). 

    Sum014.png

     

    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.

     

Answers

  • @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 .

     

    Sum001.png

  • 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,

  • @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

    PaulWestonVA_1-1580638880910.png      

    PaulWestonVA_2-1580639300369.png

     

    However in the Dashboard module PRODS is the list item dimension

    PaulWestonVA_3-1580639405247.png

    and I need to be able to get the correct answers. 

    PaulWestonVA_4-1580639540863.png

     

     

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