Set Analysis Help Required for expression

[vikasmahajan]

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

[#2]

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.

[perumal_41]

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

[jagan]

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.

[vikasmahajan]

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

[perumal_41]

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

[vikasmahajan]

Dear Mr.Perumal A,

I will test and reply you Thanks for help.

Vikas

[jagan]

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.

[vikasmahajan]

Hi,

Jagan Thanks

Insted of Var can use in set analysis direct ?

[vikasmahajan]

Hi,

Jagan Thanks

Insted of Var can use in set analysis direct ?