Add previous 2 months values on selection of particular month

[#1]

Hi Experts

I want values of previous  2 months  on selection of particular month.It should group by month wise.

On clear selection also it should display last 3 months which i got from following expression:

(Aggr(Sum({< link_Date={">=$(vLast3Months)<=$(vCurrentDate)"}, [Fiscal Year]=, [Fiscal Month]= >}

Sale),[Fiscal Year], [Fiscal Month], link_Dealer))

For example on selection i want , If i select jan 2015 then it should dislpay data of jan 2015,dec 2014,nov 2014

on the selection jan 2015 .

plzz give the solution fast its urgent. Im getting stuk in this problem.

Thanks in advance.

[sunny_talwar]

How do you define your variables-> vLast3Months and vCurrentDate?

[sunny_talwar]

Not sure why you need a Aggr for, but try this:

(Aggr(Sum({< link_Date={"$(='>=' & Date(AddMonths(MonthStart(Max(link_date)), -3), 'YourDateFormat') & '<=' & Date(MonthEnd(Max(link_date)), 'YourDateFormat'))"}, [Fiscal Year]=, [Fiscal Month]= >}

Sale),[Fiscal Year], [Fiscal Month], link_Dealer))

[#4]

(vLast3Months)=MonthStart(AddMonths(Date(Rangemin(Today()-1,Max([Calendar Date])),'DD-MM-YYYY'),-2)) 

(vCurrentDate)=Date(Rangemin(Today()-1,Max([Calendar Date])),'DD-MM-YYYY')

[#5]

Its showing result of only jan month ,on selection of jan month.

But i want it as jan 2015 ,dec 2014, nov 2014 values  .

[sunny_talwar]

See if one of these help:

(Aggr(Sum({< link_Date={"$(='>=' & Date(AddMonths(MonthStart(RangeMin(Today() -1, Max([Calendar Date]))), -2), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(RangeMin(Today() -1, Max([Calendar Date]))), 'DD-MM-YYYY'))"}, [Fiscal Year]=,[Fiscal Month]= >} Sale),[Fiscal Year], [Fiscal Month], link_Dealer))

Sum({< link_Date={"$(='>=' & Date(AddMonths(MonthStart(RangeMin(Today() -1, Max([Calendar Date]))), -2), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(RangeMin(Today() -1, Max([Calendar Date]))), 'DD-MM-YYYY'))"}, [Fiscal Year]=,[Fiscal Month]= >}Sale)

[#7]

Hi Sunny,

Your second expression is working.

But how should i use my variable in your expression.

See my 2 variables which i have posted before.

[jagan]

Hi Kajal,

If you attach the sample file it would be easier to provide the solution.

Regards,

Jagan.

[jagan]

Hi,

Try like this

Sum(Aggr(Sum({< link_Date={">=$(=vLast3Months)<=$(=vCurrentDate)"}, [Fiscal Year]=, [Fiscal Month]= >} Sale),[Fiscal Year], [Fiscal Month], link_Dealer))

Note: Both of the variables and the link_date formats should be same.

Regards,

Jagan.

[sunny_talwar · Accepted solution]

May be this:

vLast3Months = Date(AddMonths(MonthStart(RangeMin(Today() -1, Max([Calendar Date]))), -2), 'DD-MM-YYYY')

vCurrentDate = Date(MonthEnd(RangeMin(Today() -1, Max([Calendar Date]))), 'DD-MM-YYYY')

Sum({< link_Date={"$(='>=' & $(vLast3Months) & '<=' & $(vCurrentDate))"}, [Fiscal Year]=,[Fiscal Month]= >}Sale)