topic Set analysis help in QlikView

Set analysis help

sczetty — Fri, 17 Apr 2009 04:51:20 GMT

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

Fri, 17 Apr 2009 07:01:58 GMT

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

Anonymous — Thu, 15 Oct 2009 16:37:57 GMT

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_kulka — Thu, 15 Oct 2009 21:57:08 GMT

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