Set analysis year of today

[#1]

Hi all

Im trying to make a calculated dimension for a bar chart so that I only show the YearMonths where the year is the current year.

Something like this...

=only({< Year = {"=Year(today())"}>} YearMonth)

[swuehl]

You can just use a calculated dimension like

=if(Year = Year(Today()), Year)

or limit your records with set analysis in your expression aggregation functions, e.g.

=sum( {< Year = {$(=Year(today()))}>} Value)

[#3]

Follow up question....

Why doesn't this work with a +2 but it will work with a +1? The +2 keeps returning the +1 value even though its not the same.

=

count(DISTINCT{1<EXP_YEAR = {"$(=year(today()))"}
,EXP_MONTH = {"$(=month(today()+2))"}
,YHT_STATUS

-= {'CANCELATION','QUOTE','Quote',' '}>} YHT_POLICY_NBR)
& ' Policies will expire this month'

[swuehl]

Check you bracketing. Do you want to add 2 to the date of today() or to its month?

[#5]

I want to add it to the month

[swuehl]

Then you are probably looking for

$(=month(today())+2)

You might need to take care of the format of your field EXP_MONTH, though (it shouldn't be a problem if it's numeric, though doing it like this will probably lead into troubles at the end of year (when adding 2 will result in 13, 14 --> not valid month numbers. I would prefer using a timestamp as underlying numerical month number.)

Anyway, if this is still not helping you, try posting a small sample qvw and I am sure someone around will get the correct expression pretty fast.

Regards,

Stefan

[#7]

I cant post due to personal information.

Whats odd is that now that the month has changed, I cant get the +1 to work now either.

Yesterday (when it was January) the +1 worked to get February data. Now thats its february, it should give march. But now it doesn't work and just gives the current months data.

Could this have to do with date formating?

[#8]

looks like I had to use the num() function in the set analysis and as well as in the script to make the fields.

Like you said it will probably cause a problem come year end. Any idea how to avoid this?

[Oleg_Troyansky · Accepted solution]

I see quite a few opportunities for improvement here:

1. The Year of today and the Month of today are well known upfront, there is no reason to bore the user and calculate those again and again in front of him. Calculate your Calendar in the script, and add flags for Year-To-Date, Month-ToDate, Quarter-ToDate, etc... You can get fancy and calculate this month, next month, 2 months from now, etc... Assign 1 for every date that belongs to YTD, MTD, etc... and 0 ot null() for all other dates.

2. Once you do, your Set Analysis condition will look very simple:

CYTD_Flag = {1}

3. When working with Months, you don't want to use Month Number (1-12) and add or subtract to it - as you realized, you will run into a Year End problem. Instead, always operate with MonthStart() to specify your Months, and use function AddMonths() to move one or more months forward or backward from the current month. This way, you can cross years and centuries safely.

4. When using Date fields in Set Analysis, you have to provide values in exactly the same date format as the field in question. For this reason, it's sometimes easier to also keep a numeric version of the same field and compare it to a number, rather than chase a specific date format.

5. Finally, your chart will perform much better if you limit your Expressions with Set Analysis, instead of using Calculated Dimensions.

So, instead of defining a calculated dimension like:

=only({< Year = {"=Year(today())"}>} YearMonth),

use a static dimension YearMonth, and limit your expression with a similar Set Analysis condition:

sum( {<CYTDFlag={1}>} Sales)

(assuming that you implemented my earlier recommendations about pre-calculated flags)

On a large data set, you will fill a huge performance improvement from those changes.

best,

Oleg Troyansky