Rolling 12months problem

[venkey2847]

Hi all,

I have 15mnths data.

I want show rolling 12mnths data.

Dimension: Report month(2017-01,2017-02-----)

Expression: sum(retained sales)/sum(expired sales)

It is showing 100% for every month.

But it suppose to show different values.

I did Analysis find out that : the value of sum(expired sales) showing sum(retained sales) values.

Basically the values are different.

When I have 15 months data it works fine but when it comes to rolling 12 months only facing issue.

My expression: Sum({<eff-date={'=monthstart(addmonths(max(eff-date),)))

<=$(=max(eff-date))'}>} retained sales)/

Sum({<eff-date={'=monthstart(addmonths(max(eff-date),)))

<=$(=max(eff-date))'}>} expired sales).

Eff-date values are like= 01/01/2017,05/25/2017---.

Expression is fine y because same expression used for 15months data it works fine.

Please help me out this issue.

Regards

Venkey.

[Anonymous]

Hey Kumar,

Can you post the sample application.

Thanks,

Venkata Sreekanth

[venkey2847]

Sry sreekanth...

Couldn't able to share.

[Anonymous]

Hi,

Use below code in expression level.

SUM(Year_Month_New ={">=$(=Date(addmonths(Max(Year_Month_New), -11), 'YYYY-MM')) <=$(=Date(addmonths(Max(Year_Month_New), 0),  'YYYY-MM'))"} >} [YOUR FIELD])

Thanks,

Venkata Sreekanth

[MK_QSL]

but you can always create sample data having 10 to 15 lines to represent your requirements.

[venkey2847]

I am facing problem with that expression alone not with all the expression.

I resolved it by deriving rolling 12. Months in SQL and I put it in set expression then it's work fine.

But I am not sure y it is behaving that way.

Any way thanks for your responces.