topic Re: Rolling 12months problem in QlikView

Rolling 12months problem

venkey2847 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Rolling 12months problem

Anonymous — Fri, 23 Mar 2018 07:14:39 GMT

Hey Kumar,

Can you post the sample application.

Thanks,

Venkata Sreekanth

Re: Rolling 12months problem

venkey2847 — Fri, 23 Mar 2018 07:15:41 GMT

Sry sreekanth...

Couldn't able to share.

Re: Rolling 12months problem

Anonymous — Fri, 23 Mar 2018 07:17:56 GMT

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

Re: Rolling 12months problem

MK_QSL — Fri, 23 Mar 2018 08:11:36 GMT

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

Re: Rolling 12months problem

venkey2847 — Fri, 23 Mar 2018 10:16:17 GMT

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.