20 Replies Latest reply: Mar 6, 2013 4:28 AM by Eleanor Donoghue

# Rolling 12 Months HELP!

Why is my expression giving me everthing and not just the past 12 months?

Getting really bored of trying to figure it out and all other posts are either going way over my head or i can't work out what i need to adjust to make them fit my report.

Any help will be much appreciated!

Elly

sum

}>}

{1<
ITEM_UOM_GP.Brand={'2 DRAINAGE'}>}

{1<
[ITEM_UOM_GP.Reporting UOM Sub Sub Group]={'Accessories'}>}

(
Quantity )/1000)

• ###### Re: Rolling 12 Months HELP!

Maybe this:

sum

<=\$(=max([HEADERS.Posting MonthEnd]))"},ITEM_UOM_GP.Brand={'2 DRAINAGE'},[ITEM_UOM_GP.Reporting UOM Sub Sub Group]={'Accessories'}>}

(Quantity )/1000)

If not, please post the document you're working on.

• ###### Re: Rolling 12 Months HELP!

Hi

Thank you for your fast resoonse.

I've attached a censored version for you to look at.

Thanks

Elly

• ###### Re: Rolling 12 Months HELP!

Ah, you're using the monthend field as a dimension. Then set analysis won't help. The set is calculated first and then the chart expressions are applied to the set. The set is not calculated per row, so it can't take the row-value of monthend into consideration. You'll have to use what some of us call an AsOf table. See here for that approach.

• ###### Re: Rolling 12 Months HELP!

I was hoping you weren't going to say that.

I've seen those in other posts but i really don't understand what i need to do to make them work.

I need an idiots guide, but i'm guessing idiots shouldn't be using Qlikview!

• ###### Re: Rolling 12 Months HELP!

If i changed the Dimension would i still need to use 'As Of Date'?

• ###### Re: Rolling 12 Months HELP!

hi there, do u want your axis x to display at date or can it display like Jan 2013 format?

• ###### Re: Rolling 12 Months HELP!

Hi Nick

Whichever to be honest, all i'm interested in 12 rolling data and for it to state what month and year it is. Whether it's 28/02/13 or Feb 13 is not a problem.

• ###### Re: Rolling 12 Months HELP!

can you store all your table in QVD, zip it and attached it here.

• ###### Re: Rolling 12 Months HELP!

ha ha ha ha ha! That is a very good question!

I'll see what i can do, give me a few minutes.

• ###### Re: Rolling 12 Months HELP!

um no.........i've no idea how to do that from a spreadshet.

• ###### Re: Rolling 12 Months HELP!

ok, i get it.

place this script at the of your script, reload, zip the qvd, attached.

for i = 0 to nooftables()

LET d = TableName(i);

if d <> null() then

store \$(d) into \$(d).QVD;

end if

next

• ###### Re: Rolling 12 Months HELP!

done!

• ###### Re: Rolling 12 Months HELP!

errrr... where?

u need to attach it....

• ###### Re: Rolling 12 Months HELP!

I'm trying but my short fuse is getting the way of my patience today! LOL!

• ###### Re: Rolling 12 Months HELP!

Any reason why i can't add attachments when i initially post something?

• ###### Re: Rolling 12 Months HELP!

here u go... a working one, change the SET as u need to.

MONTH_SEQ is very useful, and flexible and accurate.

• ###### Re: Rolling 12 Months HELP!

Thank you very much for your help, however i am struggling to make it work this end and the only way round it is to send you the file that i am working, however it contains all our sales info and could be quite career limiting is i did send it.

I will consult my boss, to see if he can work it out.

• ###### Re: Rolling 12 Months HELP!

Hi Elly,

If you just want to view the last 12 months you can go into the Presentation tab and enter 12 as the max viewable values.  If you are on version 11 it's in the Dimension Limits tab.  Restrict to show only Last 12 values

I noticed in your example that you are pulling MonYearCalName from an excel spreadsheet which represents the various calendar values.  When that data comes into QV it's a text value and make it difficult to sort accurately.  You may want to consider creating a master calander.  If your interested you can find samples if you do a search on it in the community.  I think you will like it much better in that you will not need to keep the spreadsheet updated.

• ###### Re: Rolling 12 Months HELP!

Morning Steve

However i only have the option for First, Largest and Smallest. Need Last 12 months showing.

Could i do this within the Expression or in the Dimension?

Elly

• ###### Re: Rolling 12 Months HELP!

Good Morning Everyone!

It works!! It only blooming works!

With great thanks to Steve for pointing out the restrictions for Dimensions which led me to ask a new question of teh community.

And Nick who has created some awesome script which will provide very useful as i stumbled on how i can use it for another report i am using.

I created a blank expression to sort on first expression.