topic Re: Rolling 12 months data in QlikView

Rolling 12 months data

Fri, 28 Jun 2013 04:31:12 GMT

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.

Re: Rolling 12 months data

Fri, 28 Jun 2013 05:03:06 GMT

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)

Re: Rolling 12 months data

Fri, 28 Jun 2013 06:11:53 GMT

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.

Re: Rolling 12 months data

preminqlik — Fri, 28 Jun 2013 09:16:36 GMT

can you please explain the question in detail with example

Re: Rolling 12 months data

Fri, 28 Jun 2013 10:16:31 GMT

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 10^th, 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!