Last month of previous Quarter

[venkatg6759]

Hello Everyone,

How can i get the last month value of previous quarter based on month selection.

=Num(Sum({<year={'2013'},Qtr={'$(vPriorQtr)'}>}Sales)/1000000,' $#,##0 ').  Previous Quarter Expression

How can i include last month of previous quarter in the above.

Thanks,

Venkata

[gmoraleswit]

Hi, you can use a nested set analysis, something like this:

Num(Sum({<Year={'2013'},Month={$(=month(max({$<Qtr={$(=$(vPriorQtr))}>} Date)))}, Qtr=>}Sales)/1000000,' $#,##0 ')

see attach

[venkatg6759]

Selection should be based on month.

Your   expression is working when selecting qtr but i has month in selections

Suppose  if nov is selected then previous quarters month sale should be Sep since Q3 max month is Sep .

Thanks,

Venkata

[preminqlik]

hi there,

num(Sum({<Date={">=$(=Monthstart(Quarterend(max(Date),-1)))<=$(=monthend(Quarterend(max(Date),-1)))"},Year=,Month=>}Sales),'#,###')

[Anonymous]

Hi,

Below script may help you,

=Sum({$<DateYear={$(=Year(AddMonths(QuarterEnd(Date),-3)))}, DateMonth={$(=Month(AddMonths(QuarterEnd(Date),-3)))}>} Value)

Based on month selection, converting date to quarter end and then subtracting 3 months. You can even convert to quarter start -1 month