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

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

Wed, 16 Feb 2011 07:48:56 GMT

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?

maneshkhottcpl — Wed, 16 Feb 2011 08:00:44 GMT

Hi,

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

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

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

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

Wed, 16 Feb 2011 08:04:37 GMT

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?

maneshkhottcpl — Wed, 16 Feb 2011 08:35:20 GMT

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?

Wed, 16 Feb 2011 09:01:33 GMT

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?

Wed, 16 Feb 2011 09:57:45 GMT

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?

Wed, 16 Feb 2011 12:36:58 GMT

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?

Anonymous — Wed, 16 Feb 2011 13:58:42 GMT

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 quantity
FROM
[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?

Wed, 16 Feb 2011 14:42:05 GMT

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?

Anonymous — Wed, 16 Feb 2011 14:58:58 GMT

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?

Wed, 16 Feb 2011 16:36:57 GMT

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?

Wed, 16 Feb 2011 16:54:11 GMT

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?

Wed, 16 Feb 2011 17:36:22 GMT

Sorry Fernando, this code does not work.

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

Wed, 16 Feb 2011 17:48:11 GMT

Try this.


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

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

Anonymous — Wed, 16 Feb 2011 20:15:31 GMT

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!

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

Thu, 17 Feb 2011 13:32:45 GMT

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?

Fri, 18 Feb 2011 10:55:33 GMT

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?

tresB — Fri, 18 Feb 2011 11:17:15 GMT

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?

Fri, 18 Feb 2011 12:44:08 GMT

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?

tresB — Fri, 18 Feb 2011 12:53:07 GMT

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

good luck.

Regards, tresesco