Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

[orital81]

Hi

I am trying to create a calculation that will show me last 3 months Qty.

The complex issue here is: I need to sum Qty of 3 month back from the selection Year months

(If no selection is made then Today will be the date)

Attached my qvw File

Note: The period selection could be from either 2 sources : YM field from the table and the Year / Month selection fields on top

Thanks a lot for your time!

Ori

[CELAMBARASAN]

Hi,

     Try with this expression

     =Sum({<DateField={">=$(=MonthStart(NumMin(Max(DateField),Today()),-3))<=$(=MonthEnd(NumMin(Max(DateField),Today()),-3))"},Year=,Month=,YearMonth=>} Qty)

Hope it helps

Celambarasan

[v_iyyappan]

Hi,

     i attached sample Qv file . Hope its helpful for u.

Regards,

Iyyappan

[orital81]

Thanks Iyyappan, the set date variable is working good but

the calculation is not correct, for example : If I select Jan-2012

I expect to see 0 since there was no Qty in Oct-Dec-2011.

[v_iyyappan]

Hi,

     I Changed the expression in latest 3 months in attached file. Hope its helpful  for u .

Regards,

Iyyappan

[orital81]

Still  Jan-2012 shows Qty while

I expect to see 0 since there was no Qty in Oct-Dec-2011.

[v_iyyappan]

hi,

     Just use Latest 3 months expression.

=Sum({<Year=,Month=,YearMonth=,SetDate={'>=$(=(Num(MonthStart(AddMonths(SetDate,-3)))))<=$(=(Num(MonthEnd(AddMonths(SetDate,-1)))))'}>}TransQty)

Regards,

Iyyappan

[orital81]

That didn't help... )-:

[lironbaram · Accepted solution]

hi

check the attach file

it is working for on month year value or should it work

hope it a start

[orital81]

Hi Liron

The solution looks good though it has an issue:

We have 0 Qty in May-2011, therefore the calculation will show 0 in last 3 months

When removing the condition If(Column(1)>0 ...) then it works.

Please clarify : What is the reason for this condition?