Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Highlighted
luke1986
New Contributor III

Using Field Value as a Filter for Set Analysis

Hello,

My Example Data looks like this;

IdNameValueSum Measures
R10Test1200 
R20Test2100 
R30Test3500 
R40Test4600 
R50Test5200 
R60TestSum R10,R30,R40

 

So basically the goal is to make a Sum with the condition from "Sum Measures" where the field "TestSum" ist.

NameValue
TestSum1300

 

Is it possible to put "Sum Measures" into a Set analysis  Forumla so it makes a Sum just upon that critera?

 

Greetz Lukas

1 Solution

Accepted Solutions
MVP
MVP

Re: Using Field Value as a Filter for Set Analysis

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?

7 Replies
MVP
MVP

Re: Using Field Value as a Filter for Set Analysis

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

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

 

luke1986
New Contributor III

Re: Using Field Value as a Filter for Set Analysis

Hello 

MVP
MVP

Re: Using Field Value as a Filter for Set Analysis

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

Capture.JPG

luke1986
New Contributor III

Re: Using Field Value as a Filter for Set Analysis

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:

IdNameValueSum Measures
........
R60TestSum R10,R30,R40
..Test….. 
R100TestSum1 R20,R50

 

So to specify my question, is it possible to say something like this: 

Sum(TOTAL {< Id={[Sum Measures]}>}Value), Sum(Value))

 

MVP
MVP

Re: Using Field Value as a Filter for Set Analysis

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?

hico-mah
New Contributor II

Re: Using Field Value as a Filter for Set Analysis

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

R10Test12001
R20Test21000
R30Test35001
R40Test46001
R50Test52000

 You could than use set analysis on the CountIt field. 

luke1986
New Contributor III

Re: Using Field Value as a Filter for Set Analysis

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.