count orderlines for rolling 12 months

[fbi]

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

[stevedark]

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.

[fbi]

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]

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]

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]

Hi @fbi 

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

Steve

[fbi]

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]

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