Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Rolling 12 months data

Hi, I just started to use QlikView recently.

Could you pls give me an advice about below expression? I want to show up rolling 12month's data.

=if(sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])), sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])))

Thank you so much for your help in advance.

4 Replies
Not applicable

Re: Rolling 12 months data

FOR RELOAD TIME,

TRY THIS CODE

date(date#(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT'),'YYYY-MM')

FOR ROLLING 12 MONTH TRY THIS CODE--

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

Not applicable

Re: Rolling 12 months data

Thank you for your advice.

I'm sorry, I'm not good at to manage it. Could you pls let me know more details?

Here I add more info.

EX: Data base

YearMonth  Notif   Name Notif   Cost

201205     12345    AAAAA       100EUR

201301     12345    AAAAA       200EUR

201302     12345    AAAAA       525EUR

201305     12345    AAAAA       300EUR

201201     11111    BBBBB       350EUR

201301     11111    BBBBB       250EUR

201305     11111    BBBBB       300EUR

and I want to show up on QlikView below style.

Picture1.png

preminqlik
Valued Contributor II

Re: Rolling 12 months data

can you please explain the question in detail with example

Not applicable

Re: Rolling 12 months data

Every month, cost date by each notification No. is updating.

Ex:

YearMonth

Notification No.

Notification Name

Cost(EUR)

201203

1000

A

700

201211

1000

A

1000

201302

1000

A

525

201305

1000

A

800

201306

1000

A

950

Added on July

201201

1001

B

350

201301

1001

B

300

201305

1001

B

850

201306

1001

B

150

Added on July

We would like to see the `sum` of the cost which are only over `Total 2,000EUR`  by notification No. So I am using below expression.

=sum(if([Cost]>=2000,[Cost]))

And I would like to add one more condition which is rolling 12months.

Look at the Notification No `1000`, there are 5 different cost reports(=5 months).

However first one is little old data.

We just want to see if we open the QlikView on July 10th , each Notification No.’s cost total of rolling 12 months is calculate automatically without selecting YearMonth(201205~201306).

We want to see like below

Notification No.

Notification Name

Cost

1000

A

3275

*Notification No. 1001 is not showing up, because Cost total is less than 2000EUR.

So based on ReloadTime, we would like to see the rolling 12month cost total.

Hope you understand what I mean...Thank you so much for your help!

Community Browser