Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to build a "rolling sum" plot (or table as well), i. e. a sum that month by month runs on the latest 12 months.
More precisely, I have a graph that shows a variable (the number of sold products) month by month.
The other variable I want to show is the sum of sold products on one year up to that month.
The result should be a plot showing (month by month) the units sold in that month (e.g. March 2017) and the units sold on one year that far (e.g. April 2016 - March 2017).
I hope the request is clear.
Any help would be very appreciated.
Thanks, LuigiZ
and please refer to this thread:
where I tried to explain step by step some of the basc time functions in Qlik !
Hope these could help you Luigi !
Omar BEN SALEM
@Aehman
I want the sales over 12 month, up to the indexed month by default. The, if I select a product, he plot should show sales restricted to that product.
It's neither about sales of PYTD, nor sales YTD, it's sales on 12 months backwards.
A sample data looks like this:
Quantity | Posting Date | Product |
3 | 31/07/2017 | prod A |
20 | 28/07/2017 | prod A |
50 | 28/07/2017 | prod A |
4 | 28/07/2017 | prod A |
9 | 27/07/2017 | prod A |
7 | 26/07/2017 | prod B |
3 | 26/07/2017 | prod B |
16 | 26/07/2017 | prod B |
4 | 24/07/2017 | prod B |
30 | 21/07/2017 | prod C |
5 | 21/07/2017 | prod C |
5 | 19/07/2017 | prod C |
7 | 18/07/2017 | prod C |
54 | 18/07/2017 | prod A |
6 | 18/07/2017 | prod A |
1 | 18/07/2017 | prod A |
1 | 18/07/2017 | prod B |
2 | 04/07/2017 | prod B |
1 | 21/07/2017 | prod C |
1 | 13/07/2017 | prod C |
75 | 29/07/2017 | prod D |
40 | 28/07/2017 | prod D |
5 | 28/07/2017 | prod D |
5 | 28/07/2017 | prod D |
6 | 27/07/2017 | prod D |
1 | 26/07/2017 | prod B |
3 | 26/07/2017 | prod B |
5 | 26/07/2017 | prod B |
for monthly sold product:
DIMENSION: PostingDate.autoCalendar.YearMonth
MEASURE: Sum(Quantity)
I need to create a measure for the "Products on market" sold over 12 months backwards, month by month:
@omar bensalem, the "Calculating rolling n-period totals, averages or aggregations" looks fine, I read it earlier, but it's referred to Qlik View. How do I translae it to Qlik Sense?
all the proposed formulas don't work
The example above has been implemented with Qlik Sense; (the example with my proper master calendar)
You should have tested it... I even attached an image to show you it does what you want to achieve
omarbensalem I read your explanation on syntax and it made a lot of light, thank you very much.
unfortunately, I'm still not able to get what I need.
Ok, I didn't test it because I don't know where to paste alla that code.
Hi Luigi,
For your request you should use below expression:
=sum( {< Date = {">=$(=MonthStart(AddMonths(Today(), -12)))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Amount )
what above for time period: nov '16 - oct '17
Hi Andrea, I saw your reply, it looks fine, but it gives me all 0 as a result.
The same result I get from
sum({<Date.Autocalendar.YearMonth=,Date.Autocalendar.Year=,Date.Autocalendar.Month=,Date.Autocalendar.Date=,
Date.Autocalendar.Month={">=$(=addMonths(max(Date.Autocalendar.Date),-12))<=$(=max(Date.Autocalendar.Date))"}>}Quantity)
proposed by Omar.
One problem I realized I had, in the meanwhile, was: my field's name was "Posting Date" so in uploading to QS it became "Posting.Date" this made confusion I guess (because Date was recognized as a function).
I changed it to PostingDate
Hi Luigi,
At this point I think the quickest way to fix it is posting a sample qvf file.
How can I get it?
I'm using QS Desktop