Showing values for previous 12 months/periods using set analysis?

[#1]

Hi,

I want to make a chart with the sum of "quantity" for the current period and for 11 previous periods. I.e. 12 months/periods.

I'm using the following expression in a chart:

=

sum({$<created_per={$(=Max(created_per))}>}quantity)

created_per is the period in format YYYYMM. This expression works fine, and shows the latest period in my chart.

However, if i write expressions like this for each record:

= sum({$<created_per={$(=Max(created_per)-1)}>}quantity)

= sum({$<created_per={$(=Max(created_per)-2)}>}quantity)

= sum({$<created_per={$(=Max(created_per)-3)}>}quantity)

It does not work, because when subtracting 1 from the period 201101, it becomes 201100 - which obviously is not a valid period. Instead of 201100 I would of course need to get 201012.

Any suggestions for how to solve this? How do I refer to the previous record in the created_per "column".

[#2]

Thanks, I will play around with this to see if I can use this. I need to do some calculations in the quantity as well.

Could you share the steps for achieving this? I can see you are using "load order" as reversed. I guess that is important in order to achieve this?

[#3]

You can try this code.

Sum({$<Year= ,Month= , Date={">=$(=addmonths(monthstart(max(Date)),-11)) <=$(=(monthend(max(Date))))"}>} Sales))

[#4]

Sorry Fernando, this code does not work.

[#5]

Try this.

Sum({$<Year=, Month=, Date={">=$(=addmonths(monthstart(Today()),-11)) <=$(=(monthend(Today(Date))))"}>} quantity)

[Anonymous]

My steps?

First I made a field "MonthYear" in the loading script.

date(monthstart(@1), 'MMM-YYYY') AS MonthYear,

You need this to count the months over the years

In the sort tab I set Load order in reverse just to reverse the month, so it starts with the last month.
You could also use: sort Numeric Value as Descending.

At last, in the presentation tab, I set Max visible Number to 12, so it will show the last 12 Months.

I hope I made it a bit clear.
If you have more questions, just ask.

Good luck!

[#7]

Hi,

I have worked with ideas from the answers I received. It seems one of the keys for my issue is to load the period as Date, and I use YYYYMM - in my datasource database the value was just a string.

I am now trying to implement this with some more advanced calculations. Getting closer to success...

[#8]

Hi, I am now using Fernando's example qvw.

I now would like to make another additional bar for each period, besides the existing bar.

This bar should show the average sum of the last 3 periods. I.e. for period Feb 2011 it should show the average sum for each period Feb+Jan+Dec

In other words: 20+26+40 = 86/3 = 28.67

So Feb-2011 would have two bars: 1) Current period quantity (value 20) and 2)Previous 3 period's average quantity (value 28.67)

Is this possible to achieve? I am not able to calculate this as it seems I am not able to define the previous period in the calculation as (MonthYear-1)

[tresesco]

Hi,

i can't check what others have given in example applications. what i suggest is :

get a date field in LOAD as : LOAD Date(Date#(yourdatefield,'YYYYMM'),'MM-DD-YYYY') as Date

Then at front end use dimension : MonthName(Date), and expression as:
Sum({$<Year= ,Month= , Date={">=$(=addmonths(monthstart(max(Date)),-11)) <=$(=(monthend(max(Date))))"}>} Sales)) ...as suggested by fernando.

Hope this helps.

Regards, tresesco

[#10]

Hi Tresesco, I do have a date field (formatted as YYYYMM).

And I can show the 12 previous periods now (in Fernando's file)

but I cannot calculate the average sum for past three periods and show that as a bar besides the quantity for the current perdiod. As explained in my last post - for every period I want the current quantiy for that period, and the average for the past three periods.

I am guessing this should be a fairly common task in reporting/BI using Qlikview, but I'm having a real hard time with this, although I am learning a lot while doing it. I appreciate all help I can get.

[tresesco]

Go through RangeAvg function, i believe it would help you get done (you might require Above/Below functions as well).

good luck.

Regards, tresesco