Can i use Level-aware aggregation with a windowCount function?

Question & Answer
1

It seems that i am unable to use Level-aware aggregation with a windowCount function. I keep getting this error “VISUAL_CALC_REFERENCE_MISSING”.

My input table has:

  • Customer ID
  • Order ID
  • Order Month
  • Order Amount

i want to compute the total number of orders for each customer, in each trailing 3 months period as intermediate output. Then, i want to find the average of the intermediate output, to get final output.

I have the input table and expected results attached. Can anyone help?
input table

Intermediate output
final output

2

Hello @Shiyan !

Are of the fields you are using in the calculation going to be in your target table visualization?

3

Hi @duncan ,

Yes, the calculated field “total orders per customer in trailing 3 months” will be in the target table visualisation, with a display aggregation of ‘Average’, by Order month.

Thanks.

Regards
Shiyan

4

Hello @sushiyan ,

Can you share the calculated field that you made that gave you this error?

You could try the function below that uses the avgOver LAC-W function:
avgOver({total orders per customer in trailing 3 months}, [{Customer ID}])

5

hi @duncan ,

The calculated i used for “Total orders per consumer in trailing 3 months”
windowCount({Order ID},[truncDate(‘MM’,{Order Month}) ASC],0,2, [{Customer ID}]).

7

Hey @sushiyan ! I understand that you are using the windowCount function to get the intermediate value, but did you try the avgOver calc above to get the averages?

If the avOver function doesn’t work with the value you received from the windowCount, you could try replacing the windowCount in windowCount({Order ID},[truncDate(‘MM’,{Order Month}) ASC],0,2, [{Customer ID}]), with the countOver function.

Let me know if that helps!