Count/distinct over time

[Frank_Fu]

Hi,

I have got a question regarding counting over certain period of time and was hoping i can get some help from gurus. 

Here is my data:

MaterialNumber	CalendarMonth	InventoryPositionBuckets	Violation
40000	2019-05-01	Above
40000	2019-04-01	Above
40000	2019-03-01	Between
40000	2019-02-01	Between
40000	2019-01-01	Between
40000	2018-12-01	Below
40000	2018-11-01	Below
40000	2018-10-01	Below
40000	2018-09-01	Below
40000	2018-08-01	Below
40000	2018-07-01	Below
40000	2018-06-01	Below
40000	2018-05-01	Below

My issue is for each material number, for each month, I need to count for past 12 months (including current month), how many months have InventoryPositionBuckets as "Below" and put the number in Column "Violation".

For example, for month "2019-05-01", there are 7 months where the inventory position is "Below" between 2019-05-01 and 2018-06-01 (12 months), so the column "Violation" should show 7. it will be great if I can get this done in loading script.

Thanks in advance for your help.

[Lauri]

Hi, if you really need to do it during the load, I think you'll need to use the Peek function but I'm not entirely sure how. And if you have millions of rows, it'll take a while.

Another option is to create an AsOf table and run the calculation in a table or chart on the front end.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

This works quite well, and gives you more flexibility to change your parameters without reloading.

[NZFei]

What if you don't have 12 months history?

For example let's say your data start month is 2018-05-01, for the month 2018-06-01, are you going to count only two months 2018-05-01 and 2018-06-01?

[Frank_Fu]

Thanks Laurischarf, will try the AsOfTable option, much appreciated. 

[Frank_Fu]

Thanks Xufei, yes if there are only 2 months for any month, will need to count only 2 months. your help is much appreciated. 

[Channa]

how can you say 7 "below" for 

40000

2018-05-01

what is the logic

[Frank_Fu]

Hi Channa, for the month of "2018-05-01" the number should  show  1, as only one month (the month of "2018-05-01" itself) has status of Below, as there is no more previous month in the sample shown. Thanks.