26 Replies Latest reply: Mar 14, 2018 10:16 AM by Keti Kvirikashvili

# Set analysis - from the beginning of the year to the max date

Hello ,

I have a field month with numbers(1,2,3,4.. ) I mean ,1st is January, 2nd is February etc

When I write Month={'>=1 <=3'} in the set analysis, it shows me the results from January to March. How can I write the range if I want to start from January and to have the results before the max(date) information.

• ###### Re: Set analysis - from the beginning of the year to the max date

just a quick guest but something like this? month={">=\$(=num#(month(yearstart(now()))))<=\$(=num#(month(now())))"}

• ###### Re: Set analysis - from the beginning of the year to the max date

It doesn't work

I have data until the February 1 2018. I want to show the values from the 1st January to the February 1. I also compare other years using this formula ------- sum({\$ <Year,Month={'>=1 <= 3'}>})>} Sales) -- this calculates me January February and March Sum in all of the years. I want to change it such way  that it would show me the values until February 1

• ###### Re: Set analysis - from the beginning of the year to the max date

You may want to consider using  date oriented flags if this type of calculation is important (and obnoxious).  Generally, such flags reside on a Master Calendar, but even if they are on your Fact and Detail tables, they can provide great value.

• ###### Re: Set analysis - from the beginning of the year to the max date

do you have a master calendar table?

• ###### Re: Set analysis - from the beginning of the year to the max date

Yes

• ###### Re: Set analysis - from the beginning of the year to the max date

so should you use this in your set analysis expression:

Your_date_field = {"<=\$(=Max(Date))"}

• ###### Re: Set analysis - from the beginning of the year to the max date

May be this

Month = {"\$(='>=1<=' & Num(Month(Max(Date))))"}

• ###### Re: Set analysis - from the beginning of the year to the max date

Did you get to try this?

• ###### Re: Set analysis - from the beginning of the year to the max date

It is exactly what I wanted, but when I select year in the filter, it shows me nothing

• ###### Re: Set analysis - from the beginning of the year to the max date

May be you need to ignore selection in Year field?

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max(Date))))"}, Year>} Measure)

or this

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max({<Year>}Date))))"}} Measure)

or this

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max({<Year>}Date))))"}, Year>} Measure)

• ###### Re: Set analysis - from the beginning of the year to the max date

I used the first, but now when I select the previous years, it shows me the sum of the whole year values. Can I somehow have only the period from January to max month ??

• ###### Re: Set analysis - from the beginning of the year to the max date

Try this

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max({1}Date))))"}, Year>} Measure)

• ###### Re: Set analysis - from the beginning of the year to the max date

Thanks !!!!!!!!!!!!!!!!!! That's what I wanted !!!

• ###### Re: Set analysis - from the beginning of the year to the max date

Can you tell me how can I use this formula in bar chart, if I have dimension month and I want to compare months in these years ? If I have max month information in April, it would show me the January, February, March and April in all of these years

• ###### Re: Set analysis - from the beginning of the year to the max date

When you say these year... you mean the max year or all years?

• ###### Re: Set analysis - from the beginning of the year to the max date

All years but only these months ( from January to max month)

• ###### Re: Set analysis - from the beginning of the year to the max date

This same expression should work... isn't it working? or what is the issue that you are seeing?

• ###### Re: Set analysis - from the beginning of the year to the max date

When my dimension is month,  this formula only shows me current year's month from January to max month information. I want to show all years in these months together --- January(2014,2015,2016,2017,2018) , February(2014,2015,2016,2017,2018), March (2014,2015,2016,2017,2018), April(2014,2015,2016,2017,2018)....

• ###### Re: Set analysis - from the beginning of the year to the max date

Are you sure you are using this expression

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max({1}Date))))"}, Year>} Measure)

and not this?

Sum({<Month = {"\$(='>=1<=' & Num(Month(Max({1}Date))))"}, Year = {\$(=Max(Year)}>} Measure)

If you are using the second expression.... it will not work because you are telling your expression to look at the Max(Year).

• ###### Re: Set analysis - from the beginning of the year to the max date

I understand and I use the first formula.

It shows me only this

And I want something like this

• ###### Re: Set analysis - from the beginning of the year to the max date

I just added this, I deleted {1} from the function and it works. Now I should change the colors of these years. Thanks again!!

• ###### Re: Set analysis - from the beginning of the year to the max date

just clarify me ,you want to compare the values from jan to feb only in all years ?

• ###### Re: Set analysis - from the beginning of the year to the max date

Yes

• ###### Re: Set analysis - from the beginning of the year to the max date

if you want to compare only Jan & Feb data you can use below expression.

for currenr / max Year

=sum({<Month1={'>=1 <=2'},Year= {'\$(=max(Year))'}>}Sales)

for previous year

=sum({<Month1={'>=1 <=2'},Year= {'\$(=max(Year)-1)'}>}Sales)

• ###### Re: Set analysis - from the beginning of the year to the max date

It works But I don't want to compare to 2 , because  if I add data until the march it should change the graph by itself and I shouldn't need to change the formula