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

[maneshkhottcpl]

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)

[#3]

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

[maneshkhottcpl]

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

[#5]

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?

[#6]

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

[#7]

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

[Anonymous]

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.

[#9]

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?

[Anonymous]

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

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