Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
zagzebski
Contributor

filtering / set analysis

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
ABCJody0               2,000
ABCSteve5000
ABCGinny1000               3,000
Customer Name Producer Baseline $Current $
1000               5,000
ABCJody0               2,000
ABCGinny1000               3,000
Tags (2)
8 Replies
MVP
MVP

Re: filtering / set analysis

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.

zagzebski
Contributor

Re: filtering / set analysis

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

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)

vishsaggi
Esteemed Contributor III

Re: filtering / set analysis

Hi Sunny,

What is $+ here in the expression ?

Re: filtering / set analysis

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
zagzebski
Contributor

Re: filtering / set analysis

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.

MVP
MVP

Re: filtering / set analysis

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