Need year start and year end

[bharatkishore]

Hi All,

I have a month field and in that  i have data like jan-15 to may-16.

Now my requirement is i need to calculate data like jan -16 to may 16.

As of now i have hard coded and written the expression as

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

But i don't want like this in hard coded. I need to write based on variables.

I have given two variables like YearStart(S5_Month2) and MonthEnd(S5_Month2)..

Can you please help me how can i achieve that expression.

Thanks,

Bharat

[Anonymous]

try this

sum(if(S5_Month2>='$(vYearStart)', if(S5_Month2<='$(vMonthEnd)',([S5_Leads])))).

[bharatkishore]

Hi Linder,

Thanks for your reply and I have tried and i am getting as 0.

[saimahasan]

can you please post sample data

[marcus_sommer]

You will need a numeric period-field to make such comparings, maybe like:

Year * 100 + num(Month) as YearMonth

and then within a set analysis (performed better) something like:

sum({ < YearMonth = {">=$(vYearStart)<=$(vMonthEnd)"}>} [S5_Leads])

whereby the variables get the same format like the field.

- Marcus

[bharatkishore]

I have one query before that.. As mentioned i have one month field which contains data from Jan -15 to May -16.

First i need an variable to get Jan-16 and not data from Jan-15..Even if i select month filter also it should be starting from Jan-16

[vishsaggi]

May be try this:

In the script or in the variable

LET vYearStartDate = "=YearStart(Date#(S5_MONTH, 'MMM-YY')) ";

LET vMonthEndDate= "=MonthEnd(Date#(S5_MONTH, 'MMM-YY'))";

Expr in a table or chart like:

DIM: Your Dimensions

Expr sum(if(S5_Month2>='vYearStartDate', if(S5_Month2<='vMonthEndDate',([S5_Leads]))))

[Anonymous]

if your date format of S5_Month2  is e.g. may-16 you may need to format

sum(if(date#(S5_Month2,'MMM-YY')>='$(vYearStart)', if(date#(S5_Month2,'MMM-YY')<='$(vMonthEnd)',([S5_Leads]))))

or you define in variable vYearStart=date('1/1/2016','MMM-YY') and vMonthEnd=date('1/5/2016','MMM-YY')

[bharatkishore]

Please find the attached app..

Please let me know where i am doing wrong..

And the yearstart should be Jan -16 by default..Not Jan -15..

[Anonymous · Accepted solution]

two things:

• your datefoirmat is mm/dd/yyyy
  Change your variable vMonthEnd to =date('5/1/2016','MMM-YY')
  your variable vMonthStart donot Need to change
• you Need to encircle the variables by $()
  =sum(if(S5_Month2>='$(vMonthStart)', if(S5_Month2<='$(vMonthEnd)',([S5_Leads]))))
• TextBox showing:
• table is showing
•