topic Count/distinct over time in App Development

Count/distinct over time

Frank_Fu — Tue, 04 Jun 2019 20:18:11 GMT

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.

Re: Count/distinct over time

Lauri — Wed, 05 Jun 2019 18:58:58 GMT

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.

Re: Count/distinct over time

NZFei — Thu, 06 Jun 2019 01:18:30 GMT

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?

Re: Count/distinct over time

Frank_Fu — Thu, 13 Jun 2019 00:14:38 GMT

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

Re: Count/distinct over time

Frank_Fu — Thu, 13 Jun 2019 00:17:22 GMT

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

Re: Count/distinct over time

Channa — Thu, 13 Jun 2019 00:37:25 GMT

how can you say 7 "below" for

40000

2018-05-01

what is the logic

Re: Count/distinct over time

Frank_Fu — Thu, 13 Jun 2019 00:44:16 GMT

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.