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

Set analysis help

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

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

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)'}

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]) ,

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

Many Thanks,

Siddharth