topic Re: Help with Set analysis between 2 numbers in QlikView

Help with Set analysis between 2 numbers

Wed, 30 Jul 2014 23:24:44 GMT

Hi,

I'm trying to write an expression with set analysis that will sum the number of Sales (IsSale) if the Sale age is between 0 and 30

Here is what I have so far but it isn't working out

Sum({$<SaleAge={>=(0)<=(30)>} IsSale)

What am I doing wrong, what is the correct syntax?

Thanks for the help.

Re: Help with Set analysis between 2 numbers

maxgro — Wed, 30 Jul 2014 23:35:35 GMT

Sum({$<SaleAge={">=0<=30"}>} IsSale)

Re: Help with Set analysis between 2 numbers

VishalWaghole — Thu, 31 Jul 2014 04:49:08 GMT

Hi Brian,

Try this logic,

sum({<SalesAge = {">=0"}, SalesAge = {"<=30"}>}Sales)

this will help you and also find attached application.

-- Regards,

Vishal Waghole

Re: Help with Set analysis between 2 numbers

emiledavis — Tue, 26 Jun 2018 17:24:57 GMT

I know this post is several years old now, but I'll add to Massimo's solution (thank you by the way)...

Depending on your application and what it may be used for, using a variable for this would make the formula more dynamic. Note that the extra '=' sign and the '+0' isn't necessary. But I like to setup the expression like this because it makes shifting the value easy. I use this method to do a rolling 12 months calculation.

Sum({$<SaleAge={">=0<=30"}>} IsSale) //Static

Sum({$<SaleAge={">=$(=vMinAge-0)<=$(=vMaxAge-0)"}>} IsSale) //Dynamic

I use this method to do a rolling 12 months calculation.

Sum({$<SaleAge={">$(=vCurrentMonth-12)<=$(=vCurrentMonth-0)"}>} IsSale)

Hope this is useful to somebody out there! 🙂

Emile

Re: Help with Set analysis between 2 numbers

AndyC — Fri, 14 Jun 2024 14:04:54 GMT

Thanks for expanding this one - really useful 🙂