12 Replies Latest reply: Sep 5, 2016 3:03 PM by Bharat Kishore

# Need year start

Hi All,

I am having a field called month and in that the data like Jan-15,Feb-15.....May-16.

Now first thing i needed is I need to get Jan-16..If  i select May-16 i am getting as Jan-16 by writing the following expression

YearStart(S5_Month2) and for monthend i have given like this MonthEnd(S5_Month2)..

Now i need to write an expression like sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads])))) without hard coding..

Kindly suggest

Thanks,

Bharat

• ###### Re: Need year start

Convert your S5_Month2 to a date file using Date#() function, like

S5_Month2,

Monthstart(Date#(S5_Month2,'MMM-YY')) as S5_Month2_Date,

...

Then:

Sum(If( S5_Month2_Date >= Yearstart(Today()) and S5_Month2_Date <= /*how you derived '5/1/2016' */ , [S5_Leads]))

• ###### Re: Need year start

Thanks a lot stefan.. i have almost done but i needed slight help when i selecting the filter of May-16 by giving the following expression as :

YearStart(S5_Month2) then i am getting as Jan-16 but can you pls tell me how can i achieve Jan-15..

Because by hard coding i have written the following expression:

Now my requirement is if i select filter of month the values should display according the filter i selected in month.

Suppose if i select Apr in the filter then the expression should be

I explained you by hard coding but now i want to remove hard coding.

Kindly suggest..

Please let me know if you require anything more.

• ###### Re: Need year start

You can use the second argument to yearstart() function to get the previous year:

YearStart(date[, period_no[, first_month_of_year]])

=Yearstart(Today(), -1)

You may need to apply set analysis to be able to access records outside your current selections, though (depending on which field you make active selections and how this field is linked to your S5 fields):

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

• ###### Re: Need year start

I have attached the qvw ... pls have a look and kindly suggest.. i m bit confused.. Pls tell me how can i achieve this..

Many thanks

• ###### Re: Need year start

Sorry, I am not able to open QVWs at the moment. Maybe someone else can help you with your issue.

• ###### Re: Need year start

No problem..But i have given the following expression as suggested by you..

=Sum(If( S5_Month2 >= Yearstart(Today()) , if(S5_Month2 <=MonthEnd(S5_Month2) , [S5_Leads])))

I am not getting the right total and i m getting just total of the selected month..not all the 5 months total..

• ###### Re: Need year start

Hi Bharat,

May be like this?

• ###### Re: Need year start

Hi Settu..

Thank you so much.. this is what i needed for one expression.

And for other expression i need to get Jan-15 to May-15 if i select may-16 filter.. kindly help..

• ###### Re: Need year start

Like

• ###### Re: Need year start

I have given the following hard coding expression:

With your help i have completed the first expression i.e.

Now i need to achieve the second expression i.e.

If  i give add months and in the filter if i select May-2016 it is showing as May-15 but i need to get Jan-15 if i select May-2016.

• ###### Re: Need year start

Have you tried, what i have given for 2015? it is working for me..

and, do you still need if condition?

• ###### Re: Need year start

Thanks a lot...Sorry My bad didn't check... thank u so much...