Last 12 months expression help!

[amit_saini]

Hi Folks,

We are having one of our application in which

Dimension: = Month ([GSE Overall.GSEoverallmonth]) & '-' & Year ([GSE Overall.GSEoverallmonth])

Expression: [GSE Overall.ppm]

Below is the result table , now here we want one column to show last 12 months ppm rate.

Please suggest how to do it.

Actually we are not using any calendar for this Dashboard, otherwise I could have done something similar:

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"} >} Sales)

Thanks,

AS

[Kushal_Chawda]

what is the values in GSE Overall.GSEoverallmonth Field

[thakkarrahul01]

Hi amit,

As you mentioned you have dimension information in month-year format. Don't you have field containing date entries against each row in the table, if its the case then expression you wrote should be working fine.

Now question is , in table you have included monthly column using calculations then How you managed to calculate them using expression?

[amit_saini]

Thanks,

AS

[nagarjuna_kotha]

Hi Amit,

Try this Expression :

sum({<Date = {">=$(=MonthStart(Max(Date), -12))<=$(=MonthEnd(Max(Date)))"},Year=,Month=>}Sales).

-Nagarjun

[Kushal_Chawda]

I am not sure but you can try this

=rangesum(after( sum( [GSE Overall.ppm]), 1,12 )) or rangesum(after( sum( [GSE Overall.ppm]), 0,12 ))

or

rangesum(after( only( [GSE Overall.ppm]), 1,12 )) or rangesum(after( only( [GSE Overall.ppm]), 0,12 ))

[amit_saini]

Any suggestions???

PFA of sample qvw

Thanks,

AS

[Kushal_Chawda]

See the attached