8 Replies Latest reply: Aug 21, 2016 11:14 AM by Stefan Wühl RSS

    filtering / set analysis

    Steve Zagzebski

      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
        • Re: filtering / set analysis
          Stefan Wühl

          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
              Steve Zagzebski

              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

                  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
                      Vishwarath Nagaraju

                      Hi Sunny,

                      What is $+ here in the expression ?

                        • Re: filtering / set analysis
                          Sunny Talwar

                          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

                            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
                            Steve Zagzebski

                            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
                                Stefan Wühl

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