Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luke1986
Contributor III
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
tresesco
MVP
MVP

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?

View solution in original post

7 Replies
tresesco
MVP
MVP

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
Contributor III
Contributor III
Author

Hello 

tresesco
MVP
MVP

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
Contributor III
Contributor III
Author

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

 

tresesco
MVP
MVP

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
Partner - Contributor II
Partner - Contributor II

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
Contributor III
Contributor III
Author

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.