How to get the Difference of a distinctCount value and the value prior to it?

[Keddy]

I’m trying to get the difference of a distinct Count value to the value prior to it. I used the below formula but it showed an error “Please correct distinctCountOver to Pre_AGG or Pre_Filter level”

difference(distinctCountOver({Value},[{week},year]),[year ASC],-1)

Example:

[Tatyana_Yakushev]

Hi Keddy,
Does it work if you change expression to
difference(distinctCountOver({Value},[{week},year]),[year ASC, week ASC],-1)

[Keddy]

Thank you Tatyana, however it seems the error still exists…

[Tatyana_Yakushev]

How about
difference(distinctCountOver({Value},[{week},year], PRE_AGG),[year ASC, week ASC],-1)

[Keddy]

This doesn’t work as well. It’s really weird for “distinctCountOver”. I tried another formula and it worked now:

difference(sumOver(distinct_count(Value),[{week},year]),[year ASC, {week} ASC],-1)