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:
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:
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".
whenever u use any calculation on date, use date function.
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.
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.
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?
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
(biff, no labels, table is Sheet1$);@2 as quantity
This way you make it easier onyourself to work with months and yours.
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.