3 Replies Latest reply: Oct 15, 2009 5:57 PM by Siddharth Kulkarni RSS

    Set analysis help

    Stephen Czetty

      I have a simple set analysis formula:

       

      Sum

       

       

      ({<TxSort<{0}, Transaction={'MARKDOWNS'}, NRF_YR={$(=only(NRF_YR))}, NRF_WK_NO <={$(%currentweeknum)}>} Retail

      )

      Notice the <= relation used on the last item. This does not work as expected, the relationship is actuall ignored and all NRF_WK_NO values are considered, throwing off the numbers. However, if I just use the "=" relation, the data returned is for the one value selcted. Any ideas?





        • Set analysis help
          Anthony Deighton

          You're thinking of a set modifier as a traditional SQL query, which it is not. Think more like how you would do it in a QlikView listbox. If you have listbox with year want to sum sales for 2000, you might do:

          sum({<year = {2000}>} [Sales])

          If you want 2000 and 2004:

           

          sum({<year = {2000,2004}>} [Sales])

          You can also include searches in the set, but then you need ""s... so this would get you year 2000 and any year starting with 19:

          sum({<year = {2000,"19*"}>} [Sales])

          and... now for the actual question you asked... if you want years higher than 2002:

          sum({<year = {">2000"}>} [Sales])

          If you notice above, this is exactly the same syntax you would use if you were searching in an actual listbox... so, one easy way to build a set analysis statement is to think about how you would search for what you need in a listbox.

          As a side note... you can do even cooler things here... imagine totaling sales for all customers who had more than 3 orders:

           

          sum({<Customer = {"=count[OrderID] > 3"}>} [Sales])

          If you type =count[OrderID] > 3 into the search section of the customer listbox, you'll get a list of customers who have bought more than three orders... and the sum above is calculated against that set.





            • Set analysis help
              Martin van Niekerk

              Is there any one that can assist me with the line below:

              =Sum({$<[Account Type]={'Income Statement'},[ProjReport Sort]={'C.'}, [Job Code]={'>=$(2000)'} >} Amount)

               

              It does not work if I include the last section [Job Code]={'>=$(2000)'}

               



              • Set analysis help
                Siddharth Kulkarni

                Hi,

                With reference to your post:- As a side note... you can do even cooler things here... imagine totaling sales for all customers who had more than 3 orders: sum({<Customer = {"=count[OrderID] > 3"}>} [Sales]) ,

                 

                I was trying to pick sales people whose total sales are greater than 5000. I could achieve this using aggregation functions/flags which I want to avoid:- =if(aggr(sum(sales),name)>5000,sum(sales))

                How can I use Set Analysis to achieve the same? I tried =sum({<name = {"=sum[sales] > 5000"}>} [Sales]) , but its not working.

                Do aggregation functions work within set analysis? If no, how can I go about it?

                 

                Many Thanks,

                Siddharth