Set Analysis -less than or equal to

[zagzebski]

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))}

[jagan]

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.

[zagzebski]

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

[bgerchikov · Accepted solution]

Hi,

Try to use Num# function as follows:

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

Good luck!

[jagan]

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.

[zagzebski]

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

[bgerchikov]

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

Thanks!

[qlikpahadi07]

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!!!!

[#9]

Hi Zagzebski,

This may help you.

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

[PrashantSangle]

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,