topic Set Analysis - ignore some filters, but not others in QlikView

Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 21:22:02 GMT

I am new to Qlikview, so please keep this in mind when answering.

My problem is that I am trying to create a table that will filter the numerator to all selections, but the denominator to only some. Specifically, I need a per capita number for a subset of a population and a total cost. So I want to be able to filter the population to one thing in the denominator, and filter the costs to the population, but also to a subset of total costs.

For example, if we have total money spent on fruit by people per person. But we want money spent on oranges by women. So we need to be able to filter the denominator to women, and the numerator to women and oranges.

Thanks in advance for any help.

Re: Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 21:24:30 GMT

sum( {$<FIELD_TO_IGNORE=, FIELD2_TO_IGNORE= >}, Sales )

this should help

Re: Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 21:25:14 GMT

you can add all the fields which you want to ignore in the above expression

Re: Set Analysis - ignore some filters, but not others

swuehl — Thu, 15 May 2014 21:27:23 GMT

Maybe like

= sum({<People = {'Women'}, Fruit = {'Oranges'}>} MoneySpent) / sum({<People = {'Women'}>} MoneySpent)

or if you have made selections on People and Fruit and want to ignore section on Fruit:

=sum( MoneySpent) / sum( {<Fruit= >} MoneySpent)

Re: Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 21:31:15 GMT

sum(Sales)/ sum( {$<Fruit=>}, Sales ) with Women and Orange selected in the dashboard.

the numerator will respect your selection and will give you women who bought Oranges and the denominator will ignore the Fruit selection.

Re: Set Analysis - ignore some filters, but not others

maxgro — Thu, 15 May 2014 21:33:55 GMT

I try

sum ( {$ <sex={woman},fruit={orange}>} [money spent])

count({$ <sex={woman}>} person)

but better if you can post your qlikview doc

You can also search in help for set analysis

or read this doc Set Analysis: syntaxes, examples

or search google for set analysis wizard

Re: Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 21:52:19 GMT

Sorry Swuehl, did not mean to duplicate your reply. My page did not refresh while I was replying to this

Re: Set Analysis - ignore some filters, but not others

Thu, 15 May 2014 22:12:11 GMT

Thank you, I think I can work with your answers. I really appreciate the quick response. I was told this is an excellent forum, and was apparently told correctly.

Re: Set Analysis - ignore some filters, but not others

nizamsha — Fri, 16 May 2014 06:33:20 GMT

=sum({$<gender={"Woman"},furits={"Orange"}>}sales)/

sum({$<gender={"Women"}>}Sales)

Re: Set Analysis - ignore some filters, but not others

maxgro — Fri, 16 May 2014 12:32:52 GMT

Please close this discussion by giving Correct and Helpful answers to the posts which are useful for you. It helps others in finding answers for similar scenarios. Thanks and good Qlik.

Re: Set Analysis - ignore some filters, but not others

mansoorsheraz — Fri, 11 Nov 2022 18:29:01 GMT

You are a genius. I was looking for something similar and it worked in my scenario.