Need year start

[bharatkishore]

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

[swuehl]

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

LOAD

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

[bharatkishore]

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:

=(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='5/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='5/1/2015',([S5_Leads]))))

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

=(sum(if(S5_Month2>='1/1/2016', if(S5_Month2<='4/1/2016',([S5_Leads]))))-sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads])))))/sum(if(S5_Month2>='1/1/2015', if(S5_Month2<='4/1/2015',([S5_Leads]))))

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.

[swuehl]

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

[bharatkishore]

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

[swuehl]

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

[bharatkishore]

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

[settu_periasamy]

Hi Bharat,

May be like this?

sum({<S5_Month2={">=$(=Date(vMonthStart,'MMM-YY'))<=$(=Date(vMonthEnd,'MMM-YY'))"}>}[S5_Leads])

[bharatkishore]

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

[settu_periasamy]

Try with AddYear(Date,-1)  or AddMonths(Date,-12)

Like

sum({<S5_Month2={">=$(=Date(AddYears(vMonthStart,-1),'MMM-YY'))<=$(=Date(AddYears(vMonthEnd,-1),'MMM-YY'))"}>}[S5_Leads])