Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
My Example Data looks like this;
Id | Name | Value | Sum Measures |
R10 | Test1 | 200 | |
R20 | Test2 | 100 | |
R30 | Test3 | 500 | |
R40 | Test4 | 600 | |
R50 | Test5 | 200 | |
R60 | TestSum | R10,R30,R40 |
So basically the goal is to make a Sum with the condition from "Sum Measures" where the field "TestSum" ist.
Name | Value |
TestSum | 1300 |
Is it possible to put "Sum Measures" into a Set analysis Forumla so it makes a Sum just upon that critera?
Greetz Lukas
Hi Luke,
I am afraid that this might go on unless we can get your real business scenario or at least the all possible if-then aspects of your data. Try to share a realistic sample data set to work upon and give a try. Also, would we also consider a script modification or only a UI solution?
If you are trying to achieve this in a chart, try expression like:
If(Name='TestSum', Sum({<Id={'R10','R30','R40'}>}Value), Sum(Value))
Hello Tresesco,
My problem is that i cant hard code it, cause it can change.
So it needs to pull out the Filter Criteria Dynamically after ever reload from the field.
Well, I guess, now I get your point. Try like:
Define a variable like:
vIdSet
=chr(39)&Replace(only({<[Name]={'TestSum'}>}[Sum Measures]), ',', chr(39)&','&chr(39))&chr(39)
Then expression like:
If(Name='TestSum', Sum(TOTAL {< Id={$(vIdSet)}>}Value), Sum(Value))
Hi,
thanks for your reply, so this works if i have only one field i need to Sum. But in my specific case, i have several fields that needs to be Summed up in the same table. Like this:
Id | Name | Value | Sum Measures |
.. | .. | .. | .. |
R60 | TestSum | R10,R30,R40 | |
.. | Test… | .. | |
R100 | TestSum1 | R20,R50 |
So to specify my question, is it possible to say something like this:
Sum(TOTAL {< Id={[Sum Measures]}>}Value), Sum(Value))
Hi Luke,
I am afraid that this might go on unless we can get your real business scenario or at least the all possible if-then aspects of your data. Try to share a realistic sample data set to work upon and give a try. Also, would we also consider a script modification or only a UI solution?
Hi Luke,
I'm not sure if this is the best possible data structure to solve your problem. My approach would be to fix this within the script.
Best option, obtain a list with the relevant sum Measures, like a normal CSV. If changes are needed, this CSV can be altered and will reflect the changes on the next load. Otherwise, combine all Sum Measures values e.g. by using previous(), so you end up with a list like R10, R30, R40, R80.
Then use autogenerate and split to get a list. Either way, your end result that you create should be a table that says:
R10,1
R30,1
R40,1
Make this a mapping table and use
applyMaps('MappingTable',ID, 0)
Your final result should be
ID Name Value CountIt
R10 | Test1 | 200 | 1 |
R20 | Test2 | 100 | 0 |
R30 | Test3 | 500 | 1 |
R40 | Test4 | 600 | 1 |
R50 | Test5 | 200 | 0 |
You could than use set analysis on the CountIt field.
Yea, i guess youre right, my example was only a part of a much bigger project iam working on (Profit an Loss Calculation).
I was just curious if this is possible to handle with Set Analysis. Should have pointed that out much clearer.
Never the less, thanks for your time.