25 Replies Latest reply: Jan 10, 2012 1:55 PM by Debbie Pyykkonen

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

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".

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

Hi,

whenever u use any calculation on date, use date function.

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

or

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

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

Hi Manesh,

this also works until January (201101). When period is 201101 and I subtract 1, the result is that nothing shows on the graph as it tries to show period 201100 - instead of 201012.

Any workarounds?

Thanks

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

HI,

The Date is not in datetime formate its in numeric formate in ur application.

Firsr u have to convert ur date in proper date formate using date(makedate(left(DateField,4),mid(DateField,5,2),right(DateField,2)),'YYYYMMDD')

better to use in variable.

If confussion please send me the ssample application i will surly help u.

Thanks

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

I think I understand the point - if it is not in date format, qlickview will not be able to understand that subtracting 1 from January (201101) value should give December (201012) and not 201100.

I do have another field created_date with the exact date already in correct format. I should use this instead.

But I'm not sure how to sum all quantity for one month and previous month. Should I determine the current month, and then subtract 1 from that?

Can I use Month() function in anyway? Or do you have a good solution?

In my code example in the first post, the field created_per should be changed to created_date. This will give the exact date, but how do I now calculate this per month?

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

Manesh, or somebody else. I have attached a sample file with data for 2010 and 2011.

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

I can't believe this is so hard to achieve in QV, I have spent two days with this... [:'(]

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

Hi W.

I would start by chaging your script into something like this:

`LOAD Date(@1) as Date, Year(@1) as Year, Month(@1) as Month, @2 as quantityFROM[Book1.xls](biff, no labels, table is Sheet1\$);@2 as quantity`

This way you make it easier onyourself to work with months and yours.

See document.
Good luck.

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

Hi Dennis, thanks.

OK, so we load the dates as Months/Years, but that does not solve the problem.

The problem is, how do I present a bar chart with current month's sum of quantity, previous month's sum of quanity, and so on for a total of 12 previous months?

So far we only have the present month value, but we need the "rolling 12" values as well, i.e. the sum for each month for the previous 12 months in a barchart.

Any ideas?

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

You can try this code.

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

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

Sorry Fernando, this code does not work.

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

If I understand you correct the answer is in the new document.

Let me know if this is what you are looking for ok?

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

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?

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

My steps?

`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!

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

would be helpful if the chart would have option to show LAST 12 values, to do it this way you would have to sort descending

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

Try this.

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

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

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)

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

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...

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

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`
• ###### Showing values for previous 12 months/periods using set analysis?

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.

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

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

good luck.

Regards, tresesco

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

Hi,

I am attaching my understanding of how to use RangeAvg,

it seems it is not working in the chart as I had hoped. It gives some result, but the result is not the average of quantity for past 3 bars.

W

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

Hi,

modify the formula as : Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 RangeAvg(Above(Sum(quantity),0,3)) or something like that.

Regards, tresesco

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

Hi, that seems to give an incorrect anser. I also tried RangeSum to see if it calculates corect:

=

RangeSum(Above(Sum(quantity)),0,3)

It does not work... :( Any suggestions, I'm pretty much stuck here.

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

Hi, that seems to give an incorrect anser. I also tried RangeSum to see if it calculates corect:

=

RangeSum(Above(Sum(quantity)),0,3)

It does not work... :( Any suggestions, I'm pretty much stuck here. Attached file with example.

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

Hi,

Might be you are having a mistake with parenthesis, it would like RangeAvg(Above(Sum(quantity),0,3))

NOT as you wrote . please check it.

regards, tresesco