Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
I have following expression for like wise comparision between 28/02/2013 vs 29/02/2012 (Current Year and Previous Year)
but when I compare 28 feb month with last year 28 likewise analysis is wrong , I want to show sales of current year feb with last year
feb as we know last year feb was leap year
Can any one help me in this regards.
My expression is follows:
=(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}
[Stat Sales Amount])
-
SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}
[Stat Return Amount])
)/100000
Thanks
Vikas
Dear Sir,
Thanks for giving lot of help me first , I have try your expression but what I want is like wise comparision
means if today is 18 -mar-2013 vs 18-Mar-2012 data should display in your case last year month full
data is showing which is resulting wrong growth
Please see attachment of screen short for the same,.
Thanks again
Vikas
Hi Vikas,
You can tackle it through load script:
SQL Tips:
SELECT * FROM <Date_table>. WHERE DATE_FORMAT(<Date_table>.Posting Date, '%m') = MONTH(CURDATE())-1 AND DATE_FORMAT(<Date_table>.Posting Date, '%Y') = YEAR(CURDATE())-1
OR in Dimension you can control:
=if( InMonth(Posting_date, now(0), -12 ) , Posting_date)
OR There might be other factors in set expression that are violating. please upload file for more help
Please mark answer Helpful or correct if it work for you.
Hi Vikas,
Try below Expression
(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear-1))}>}[Posting Date]))"}>}
[Stat Sales Amount])
-
SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear)-1)}>}[Posting Date]))"}>}
[Stat Return Amount])
)/100000
I don't have Application ,so I am tested .this helpful ,i thing.
Regards
Perumal A
Hi Vikas,
Use MonthEnd() in your expression instead of AddMonths()
=Addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))
with this
=MonthEnd(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))
MonthEnd function takes care of the months end date.
Hope this helps you.
Regards,
Jagan.
Dear Jagan
using monthend function in expression I am not able to compare 15-Mar Vs 15-Mar-12 like wise comparision
since I used monthend in last year column it is taking full month data for lastyear hence there is wrong growth calculated in report.
Is there any way to resolve 28-feb-2013 vs 28-feb & 29-Feb 2012 (Last year) likewise comparision.
Please help
Thanks
Vikas
Hi Vikas,
are u facing any problem this ex[pression
(SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear-1))}>}[Posting Date]))"}>}
[Stat Sales Amount])
-
SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},
[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear)-1)}>}[Posting Date]))"}>}
[Stat Return Amount])
)/100000
Regards
Perumal A
Dear Mr.Perumal A,
I will test and reply you Thanks for help.
Vikas
Hi Vikas,
Calculate the date in variable
vDate = If(Max([Posting Date]) = MakeDate(Max(Year), 2, 28), MonthEnd(Addmonths(Max([Posting Date]), -12)),
Addmonths(Max([Posting Date]), -12))
Now use this variable in your set analysis expression.
Hope this helps you.
Regards,
Jagan.
Hi,
Jagan Thanks
Insted of Var can use in set analysis direct ?
Hi,
Jagan Thanks
Insted of Var can use in set analysis direct ?