3 Replies Latest reply: Apr 22, 2009 8:44 PM by Karl Pover RSS

    Trouble with Set Analysis

      Can someone tell me the difference between these two expressions because i can't work it out ;) BUT they give me different figures (the 2nd one gives me the correct figure). What i'm trying to get is the sales for the company regardless of a selection (in this case the sales rep). The first expression seems to take into account the reps figures somehow...

      =sum({1<InvoiceDate={"=inmonth(InvoiceDate,Today(),0,7)"}>}Value)

      AND

      =sum({<SalesRep=>} if(inmonth(InvoiceDate,Today(),0),Value))

      The data structure is very simple with a sales table (InvoiceDate) and a customer table (SalesRep)

        • Trouble with Set Analysis
          Karl Pover

          The nested expression inmonth is over the filtered selection even though sum is over the whole universe. For example,

          =sum({1<Year={"=max({1} Year)"}>} Sales)

          will not change regardless of the selection, but

          =sum({1<Year={"=max(Year)"}>} Sales)

          will change when certain selections are made that filter the Year indirectly.

          Now, it's just a matter of seeing how to apply the function inmonth over the whole universe of datos.

          Pover

            • Trouble with Set Analysis

              Thanks Pover for clearing that up... any ideas how to get inmonth({1}...)

              Would something like this work (taking your example)??

              =sum({1<Year={"=aggr({1} max(Year),SalesRep)"}>} Sales)

               

                • Trouble with Set Analysis
                  Karl Pover

                  Rob,

                  It should be simpler than using a aggr. Taking your example again and given you have or can create an additional field in the data cloud that looks something like the following:

                  month(InvoiceDate) as InvoiceMonth;

                  the following formula should work and be completely independent of the selections you make:

                  =sum({1<InvoiceMonth={$(=month(today()))}>} Value)

                  Tell me how it goes.

                  Pover