Rolling 30 Days Counts in 2 Dimensions

[qliksensedlin]

Hi,

I have a chart that I need to count the score over past 30 days by products. In table form, it looks like below.

submit_date	Product_Master_Code	Score
2019-01-01	A	21
2019-01-01	B	48
2019-01-01	C	30
2019-01-02	A	26
2019-01-02	B	23
2019-01-03	A	24
2019-01-03	C	38
2019-01-04	A	34
2019-01-04	B	30
2019-01-14	C	39
...	...	...

I tried with the Aggr expression below.

=Aggr(RangeSum(Above(Count(Score), 0, 30)),Product_Master_Code, (submit_date,(NUMERIC, ASCENDING)))

It could give me the rolling 30 days score count for any particular submit date with each product.

However, the problem is I do not have a record for all products on all dates. This would give me a miss count as the result is just 30 rows above current record regardless the dates. In effect, I could be counting over 2 month's scores if there are a few days missing in between.

What I hope to achieve is a range count between (submit_date-30) and submit_date. So I tried the following expression.

=Count({<submit_date = {">=(=submit_date-30)<=(=submit_date)"}>}Score)

This counts to 0. I assume some error with my expression.

Could any one help me with this rolling 30 days calculation?

Thank you in advance for the help.

[ashishkalia]

You need to have a master calendar.
Once you create master calendar you'll have a continuous date field and for the dates where you don't have and transaction you'll get zero and calculation will happen accordingly.
After That Formula Should be:
Count({<
submit_date = {">=$(=Date(MAX(submit_date)-30))<=(=Date(Max(submit_date)))"}
>}Score)

[qliksensedlin]

Hi Ashishakalia,

Thanks for your reply. I tried with your formula. What it gives me is only a daily count on the last 30 days. I got counts on Jan 26 to Feb 25 only. And they are not rolling 30 days counts, but just a daily count. 

What could go wrong here?

=Count({<submit_date = {">=$(=Date(Max(submit_date)-30))<=$(=Date(Max(submit_date)))"}>}Score)

[ashishkalia]

Can you pl share the output which you want and the one which you are getting now.

[qliksensedlin]

Hi Ashishkalia,

The output I would like to have:

submit_date	Product_Master_Code	Score	Rolling Count
2019-01-01	A	21	21
2019-01-01	B	48	48
2019-01-01	C	30	30
2019-01-02	A	26	47
2019-01-02	B	23	71
2019-01-03	A	24	71
2019-01-03	C	38	68
2019-01-04	A	34	105
2019-01-04	B	30	101
2019-01-14	C	39	107
...	...	...	...

The output I'm getting now:

submit_date	Product_Master_Code	Score	Rolling Count
2019-01-01	A	21	0
2019-01-01	B	48	0
2019-01-01	C	30	0
2019-01-02	A	26	0
2019-01-02	B	23	0
2019-01-03	A	24	0
2019-01-03	C	38	0
2019-01-04	A	34	0
2019-01-04	B	30	0
2019-01-14	C	39	0
...	...	...	...
2019-02-24	A	20	20
2019-02-24	B	35	35
2019-02-25	A	32	32
2019-02-25	C	30	30

The rolling count is 0 for dates more than 30 days ago.  And the counts are not accumulated for dates within 30 days.

[qliksensedlin]

Also putting up my script for master calendar creation. The rolling 30 days still can't be solved after implementing master calendar.

Temp:
Load
 min(submit_date) as minDate,
 max(submit_date) as maxDate
Resident NPS;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD
 $(varMinDate) + Iterno()-1 As Num,
 Date($(varMinDate) + IterNo() - 1) as TempDate
 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
 TempDate AS submit_date
Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;