Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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
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)
Hi Sunny,
What is $+ here in the expression ?
I guess we can just do this without a plus sign:
Sum({$<Key = {"=Sum(Current) > 0"}>}Baseline)
I think I over thought this
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
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.
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 ...)