Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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?
Thanks Laurischarf, will try the AsOfTable option, much appreciated.
Thanks Xufei, yes if there are only 2 months for any month, will need to count only 2 months. your help is much appreciated.
how can you say 7 "below" for
40000 | 2018-05-01 |
what is the logic
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.