topic Re: Using Field Value as a Filter for Set Analysis in App Development

Using Field Value as a Filter for Set Analysis

luke1986 — Wed, 19 Dec 2018 07:18:56 GMT

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

Re: Using Field Value as a Filter for Set Analysis

tresB — Wed, 19 Dec 2018 07:34:52 GMT

If you are trying to achieve this in a chart, try expression like:

If(Name='TestSum', Sum({<Id={'R10','R30','R40'}>}Value), Sum(Value))

Re: Using Field Value as a Filter for Set Analysis

luke1986 — Wed, 19 Dec 2018 07:46:46 GMT

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.

Re: Using Field Value as a Filter for Set Analysis

tresB — Wed, 19 Dec 2018 08:08:41 GMT

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

Re: Using Field Value as a Filter for Set Analysis

luke1986 — Wed, 19 Dec 2018 08:25:04 GMT

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

Re: Using Field Value as a Filter for Set Analysis

tresB — Wed, 19 Dec 2018 08:34:34 GMT

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?

Re: Using Field Value as a Filter for Set Analysis

hico-mah — Wed, 19 Dec 2018 13:01:51 GMT

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.

Re: Using Field Value as a Filter for Set Analysis

luke1986 — Wed, 19 Dec 2018 15:15:39 GMT

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.