Rolling NPS

[#1]

Hi,

I am trying to do a rolling NPS (sum over 7 days).

I have date as my dimension.

I can do normal NPS:

=num(

(sum({<Date = {"<=$(=date(max(Date)))>=$(=date(monthstart(max(Date))))"},Score={9,10}>} FeedbackFlag)/sum({<Date = {"<=$(=date(max(Date)))>=$(=date(monthstart(max(Date))))"}>} FeedbackFlag)*100)

-

(sum({<Date = {"<=$(=date(max(Date)))>=$(=date(monthstart(max(Date))))"},Score={0,1,2,3,4,5,6}>} FeedbackFlag)/sum({<Date = {"<=$(=date(max(Date)))>=$(=date(monthstart(max(Date))))"}>} FeedbackFlag)*100)

,'0.0')

I want t get the NPS score between dimension date and dimension date -7

=num(

(sum({<Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-7))"},Score={9,10}>} FeedbackFlag)/sum({<Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-7))"}>} FeedbackFlag)*100)

-

(sum({<Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)))"},Score={0,1,2,3,4,5,6}>} FeedbackFlag)/sum({<Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-7))"}>} FeedbackFlag)*100)

,'0.0')

But in the chart this is just giving me a data value for the last 7 days, the rest of the MTD graph is showing '-'.

Can anyone help?

[Gysbert_Wassenaar · Accepted solution]

See this document for an explanation and some solutions: Calculating rolling n-period totals, averages or other aggregations

[#3]

Hi Gysbert, that has worked for me - thank you