Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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])
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.
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])
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.
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)'}
Many Thanks,
Siddharth