Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
fbi
Contributor II
Contributor II

count orderlines for rolling 12 months

Hi, 

I have a table with:

ItemNo, Date (YYYYMM), qty

This table shows customers order lines so I want to count the number of order lines using ItemNo for rolling 12 months.

I tried several solutions that I found in this forum but it doesn't work since it shows just the no of order lines for each months. I'm using calendar and btw it doesn't shows the months with 0 order lines, also something that I couldn't fix.

Any kind of help will be high appreciated

Labels (2)
8 Replies
stevedark
MVP
MVP

Hi @fbi 

This post may point you in the right direction:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Steve

PrashantSangle

can you explain with sample data?? and also post required output.

Great dreamer's dreams never fulfilled, they are always transcended.
fbi
Contributor II
Contributor II
Author

Hi, following is my script:

order:
LOAD
[LAFÖRE],
[LAART],
Date(Date#([LADATU],'YYYYMMDD'),'YYYYMMDD' ) AS [LADATU],
[LASKOD],
([LAANTA] * -1) AS 'Antal',
[LAFILI]
FROM [lib://1_Data/1_Extract/Trygg/TRLLA00.qvd]
(qvd)
WHERE [LASKOD] = 'F';

----------------------------------

so the result for example imagining that I just have 2 item no (item A and item B) should be following:

LADATU (Date)  LAART (Item No)           Orderlines R12 months

2021-01-08               A                                     150                Count LAART for period from 2020-01-09 to 2021-01-08)

2021-01-08               B                                     180                Count LAART for period from 2020-01-09 to 2021-01-08)

2021-01-07               A                                     147                Count LAART for period from 2020-01-08 to 2021-01-07)

2021-01-07               B                                     182                Count LAART for period from 2020-01-08 to 2021-01-07)

...and so on with the other dates

 

stevedark
MVP
MVP

Hi @fbi 

So, you want to create a month dimension in the load script with a preceding load, like this:

order:
LOAD
   *,
   Date(MonthStart(LADATU), 'MMM-YY') as Month
   ;
LOAD
[LAFÖRE],
[LAART],
Date(Date#([LADATU],'YYYYMMDD'),'YYYYMMDD' ) AS [LADATU],
[LASKOD],
([LAANTA] * -1) AS 'Antal',
[LAFILI]
FROM [lib://1_Data/1_Extract/Trygg/TRLLA00.qvd]
(qvd)
WHERE [LASKOD] = 'F';

 

You can then follow the code in the blog post to build the MAT table:

for vMAT = 0 to 11
     MAT:
     LOAD
          Month,
          Date(AddMonths(Month, $(vMAT)),'$(vMonthFormat)') as MAT
     RESIDENT order
     WHERE AddMonths(Month, $(vMAT)) <= today();
next

 

Then use MAT as the dimension on your chart and it will roll up 11 previous months with each month.

Steve

fbi
Contributor II
Contributor II
Author

Hi, thank you very much for your reply.

I did as per your instruction but I get the attached error message (error_message.PNG)

I also attach the new script (Script.PNG)

stevedark
MVP
MVP

Hi @fbi 

It looks like you have some rogue square brackets on the WHERE AddMonths line of code?

Steve

fbi
Contributor II
Contributor II
Author

Great Stevedark! It works now! But still I don't know how to calculate rolling 12 months order lines using Count(LAART). Maybe you have an idea about how to solve it?

Thanks a lot for your help!

stevedark
MVP
MVP

Hi @fbi 

If you use MAT as the dimension and do Count(LAART) as the expression then you should get the rolling twelve month total in each cell.

By selecting a single month, over 12 months old, you should see that it appears in the following 12 MAT months, and similarly if you select a single MAT month you should see that this includes the preceding 11 months.

Personally I would add 1 as OrderCount, to each order line in the load, then the expression in the table becomes sum(OrderCount). It should give you the same result as Count(LAART), but it should be slightly more efficient.

Hope that helps,

Steve