topic Re: filtering / set analysis in QlikView

filtering / set analysis

zagzebski — Fri, 19 Aug 2016 14:15:37 GMT

I want the following: to be able to filter on the baseline $ column but not have that filter affect any of the numbers in the current $ column like in the chart below. I have tried set analysis on the current $ column to exclude any filter from Producer but it still keeps filtering out the "Jody" producer because she has no baseline $ amount.

Customer Name	Producer	Baseline $	Current $
		1500	5,000
ABC	Jody	0	2,000
ABC	Steve	500	0
ABC	Ginny	1000	3,000

Customer Name	Producer	Baseline $	Current $
		1000	5,000
ABC	Jody	0	2,000
ABC	Ginny	1000	3,000

Re: filtering / set analysis

swuehl — Fri, 19 Aug 2016 21:17:04 GMT

Sorry Steve, I don't understand what you are trying to achieve.

Is the second table what you are trying to achieve? Can you describe which filter you applied to Baseline $ column?

Maybe post a more complete description of your current data model (best by posting a small sample QVW) and a description of your required result.

Re: filtering / set analysis

zagzebski — Sat, 20 Aug 2016 13:51:50 GMT

Sorry Stephan will send a model if my description below doesn't help.

Very simply - when I filter on "Ginny", then "Jodi" goes away in my chart. I want Jodi to stay because she has dollars in the Current $ column. I am struggling to find the set analysis the will force any producer to rename (no matter what producer is filtered on) if they have an amount in the Current $ column.

Steve

Re: filtering / set analysis

sunny_talwar — Sat, 20 Aug 2016 14:01:55 GMT

May be like this:

In the script, create a new column

LOAD [Customer Name],

Producer,

AutoNumber([Customer Name]&Producer) as Key,

Baseline,

Current

FROM....

and then an expression like this:

Sum({$+<Key = {"=Sum(Current) > 0"}>}Baseline)

Re: filtering / set analysis

vishsaggi — Sat, 20 Aug 2016 16:19:42 GMT

Hi Sunny,

What is $+ here in the expression ?

Re: filtering / set analysis

sunny_talwar — Sat, 20 Aug 2016 16:59:56 GMT

I guess we can just do this without a plus sign:

Sum({$<Key = {"=Sum(Current) > 0"}>}Baseline)

I think I over thought this

Re: filtering / set analysis

Anil_Babu_Samineni — Sat, 20 Aug 2016 17:04:57 GMT

Sum({$+<Key = {"=Sum(Current) > 0"}>}Baseline)

Sum(Baseline) -- Nothing but, We are calculating the Sum for Baseline

Sum({<Key = {"=Sum(Current) > 0"}>}Baseline)

---------------------------------------------------------------------------

So, Here Same thing calculating here where Key = Total Sum of Current more than 0

Key is indicating the Auto number

$+ Is nothing but, Current Selection where the Above Expression. Here, Two expressions acting the MERGE

Note: Please correct me, If my explanation is WRONG

Re: filtering / set analysis

zagzebski — Sun, 21 Aug 2016 14:23:23 GMT

Thanks Sunny -

Actually the Baseline column is a simple Sum(Baseline)...I want it to respect any filters. My issue is I want the Current $ column to disregard any filters if there is an amount greater than 0. In my example above the Jody record goes away when I filter on Ginny.

Re: filtering / set analysis

swuehl — Sun, 21 Aug 2016 15:14:18 GMT

Doesn't Sunny's first expression work for you, Steve?

(Maybe you don't need to create the Key field, that depends on the granularity you want to aggregate a producer's current value.

I am still confused about what you want to achieve. Do you want the two expressions for baseline and current behave differently in the same chart?

It's much easier to help with some real data & model to look at and some use cases (if I select Jody, I want Ginny to stay, because ... On the other hand, if I select ... I want ...)