topic Re: Less then equal to not working in set analysis in QlikView

Less then equal to not working in set analysis

Anonymous — Tue, 27 Aug 2013 09:29:56 GMT

Hi Geniuses

I have a problem in set related with less then equal to

sum({$<Year = {$(vThisYear)}, Month = {"<=$(=vThisMonthText)"}>} [Product Bookings Net])

Above formula is not working when I am using less then equal to after month

require ur assistance

Thanks,

Shashnak

Re: Less then equal to not working in set analysis

Miguel_Angel_Baeyens — Tue, 27 Aug 2013 09:34:28 GMT

Hi Shashnak,

Since your Month is in text, and less than or equal to is a numeric operator, this will not work. You will need to create an additional field in the script where the Month is stored in numeric value, like the Year, and use this new numeric field in the variable. That will fix it.

Hope that helps.

Miguel

Re: Less then equal to not working in set analysis

Tue, 27 Aug 2013 09:37:49 GMT

Hi Shashnak,

Can you attach a sample document? You may need an = in front of vThisYear What about:

sum({$<Year = {$(=vThisYear)}, Month = {"<=$(=vThisMonthText)"}>} [Product Bookings Net]) or

sum({$<Year = {"$(=vThisYear)"}, Month = {"<=$(=vThisMonthText)"}>} [Product Bookings Net])

Have you tried that?

Andy

Re: Less then equal to not working in set analysis

CELAMBARASAN — Tue, 27 Aug 2013 09:39:22 GMT

Hi,

If you created a field with Month() function. Then you can use <= operator but you should compare this with string.

May be something like this

Assuming that

vThisMonthText = Max(Month)

where Month field arrived from Month(DateField)

sum({$<Year = {$(vThisYear)}, Month = {"<=$(=vThisMonthText)"}>} [Product Bookings Net])

Re: Less then equal to not working in set analysis

rbecher — Tue, 27 Aug 2013 09:39:23 GMT

This wont work since vThisMonthText is not numeric: '2' > '10'

Re: Less then equal to not working in set analysis

Tue, 27 Aug 2013 09:40:16 GMT

Or you could use the Dual function right? Something like Dual(month, autonumber(month)) as month.

Re: Less then equal to not working in set analysis

MayilVahanan — Tue, 27 Aug 2013 09:40:26 GMT

Try like this

Use DateField, i think Month is in text format

SUM({$<DateField = {"<=$(=MonthStart(max(DateField)))"}>} [Product Bookings Net])

Hope that helps

Re: Less then equal to not working in set analysis

kalyandg — Tue, 27 Aug 2013 10:28:01 GMT

hi,

try this

sum({$<Year = {$(vThisYear)}, Month = {"<=$(vThisMonthText)"}>} [Product Bookings Net])

Thanks,

Kalyan

Re: Less then equal to not working in set analysis

Anonymous — Tue, 27 Aug 2013 10:50:26 GMT

Hi Geniuses,

Please find attached qvw which I am refering to calculate YTD MTD

In this go the sheet set analysis static and go the pivot table at bottom u can see the formula there

he is comparing the text month with the number and getting desired result

in my case the variables are below

vThisMonth is number

vThisMonthText is text lik Aug sep

Month dimension is Aug Sep

now please enlighten me guys

Thanks

Re: Less then equal to not working in set analysis

tresB — Tue, 27 Aug 2013 11:02:57 GMT

Dear Shashank,

As already told by Celambarasan Adhimulam, you can use '<=' operator if the month values are created using Month() function (you can notice the same in your referred application). So it could be a major point of consideration how you are creating this month field. Best way people here can help you, if you can upload your sample application.

Re: Less then equal to not working in set analysis

CELAMBARASAN — Tue, 27 Aug 2013 11:07:22 GMT

Hi,

If you are having date field you could make use of that.

like the one below for YTD previous year

sum({$<Year = , Month =, Date={">=$(=YearStart(AddYears(vToday,-1)))<=$(=AddYears(vToday,-1))"}>} LineSalesAmount)

Probably current year would be

sum({$<Year = , Month =, Date={">=$(=YearStart(vToday))<=$(=vToday)"}>} LineSalesAmount)

Hope that helps

Celambarasan