topic Re: Set Analysis -less than or equal to in QlikView

Set Analysis -less than or equal to

zagzebski — Fri, 14 Mar 2014 02:04:01 GMT

I have the following in my set analysis expression and it works fine. However I am struggling with the syntax of this part of the expression when I want to change it to MonthYear being less than or equal to the right side of the equation. I don't know where to put the <=

MonthYear={$(=max(MonthYear))}

Re: Set Analysis -less than or equal to

jagan — Fri, 14 Mar 2014 02:09:24 GMT

Hi,

If you have a date field then you can do like this

=SUM({<DateDimensionName={'<=$(=Date(Max(DateDimensionName)))'}>} Measure)

Replace DateDimensionName with your date field and confirm you Date field values and Today() value are in the same format if not convert the Date format in above expression.

Regards,

Jagan.

Re: Set Analysis -less than or equal to

zagzebski — Fri, 14 Mar 2014 02:19:16 GMT

Thanks for the response.

However this is not a date field. It is a text field (201401, 201402, etc).

How would this change the above?

Steve

Re: Set Analysis -less than or equal to

bgerchikov — Fri, 14 Mar 2014 02:32:04 GMT

Hi,

Try to use Num# function as follows:

MonthYear={"<=$(=max(Num#(MonthYear)))"}

Good luck!

Re: Set Analysis -less than or equal to

jagan — Fri, 14 Mar 2014 02:33:36 GMT

Hi,

You can arrive a date field in script like this

Load

MakeDate(Left(MonthYear, 4), Right(MonthYear, 2)) AS DateDimensionName

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis -less than or equal to

zagzebski — Sun, 16 Mar 2014 17:34:07 GMT

Thanks Boris. Unfortunately I inserted your expression into the set analysis and it doesn't return anything.

Re: Set Analysis -less than or equal to

bgerchikov — Mon, 17 Mar 2014 03:49:25 GMT

Can you attach a sample app to review? In my test it works perfect.

Thanks!

Re: Set Analysis -less than or equal to

qlikpahadi07 — Mon, 17 Mar 2014 04:55:19 GMT

Hi Zagzebsi,

well you have to make text to Num as Jagan had already suggested how to do that.

if you need to put '<=' it should be numbers as machine can give you greater or lesser in numbers not in test.

cheers!!!!

Re: Set Analysis -less than or equal to

Mon, 17 Mar 2014 05:12:05 GMT

Hi Zagzebski,

This may help you.

MonthYear={<{'<=$Max((MonthYear))'}>}

Re: Set Analysis -less than or equal to

PrashantSangle — Mon, 17 Mar 2014 06:02:43 GMT

Hi,

Try this,

=Sum({<MonthYear={"<=$(=max(MonthYear))"}>}Measure)

OR You can do convert your MonthYear Field from text to date

using Date(Date#(MonthYear),'YYYYMM'),'YYYYMM')

then use

expression given by Jagan Mohan in your set analysis.

Regards,

Re: Set Analysis -less than or equal to

sunilkumarqv — Mon, 17 Mar 2014 06:55:17 GMT

Hi Zagzebski

Try like these

MonthYear= {"<=$(=Max(MonthYear))"}

Hope you get the solution

make your dimension as Date(MonthYear)

and use Date ={"<=$(=Max(Date))"} in these format