Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
two things:
try this
sum(if(S5_Month2>='$(vYearStart)', if(S5_Month2<='$(vMonthEnd)',([S5_Leads])))).
Hi Linder,
Thanks for your reply and I have tried and i am getting as 0.
can you please post sample data
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
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
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]))))
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')
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..
two things: