Level Aware Calculation (LAC) agg functions can not be used as operands of LAC window functions

Question & Answer
,
1

I am writing the below but I get this error

 sumOver(max({Submitted Amount},[{Expense ID}]), [{Appointment ID}], PRE_FILTER)

In the submitted amount I have duplicates so what I am trying to achieve is to deduplicate the submitted amount by taking the max and then find the sumover by appointment ID.
how can this work?

Thanks

2

Hi @pantelis,

Is Appointment ID a dimension in your visual? If it’s in your visual, you can just use sum instead of sumOver.

sum(max({Submitted Amount},[{Expense ID}]))

Are you using sumOver because you need PRE_FILTER and sum doesn’t support it?

3

Hi @David_Wong
No appointment id is not a dimension in the visual but I want to calculate the sum before applying the filters.

I have another dimension called type which takes values (Preliminary/Final) and I want to create a table with the below columns and filter Final

Case_ID Preliminary_amount Final_amount

The Case_ID is final but I want in the same row to show also what was the corresponding amount of the preliminary to make a comparison.

when I use

sum(max({Submitted Amount},[{Expense ID}]))

the Preliminary amount is null because I have the Final filter.

The below is working fine but I have some duplicates and the total is not exactly correct

 sumOver(Submitted Amount}, [{Appointment ID}], PRE_FILTER)

Thanks

4

You can’t have an aggregation inside sumOver. If you really want to use sumOver, you have to replace the max by maxOver. The problem is that the same value will be repeated in every row of your partition. To cancel out the duplicates, you would then have to divide by the number of rows in your partition.

I think this should work:

sumOver(
	maxOver({Submitted Amount},[{Expense ID}], PRE_FILTER) / countOver({Expense ID},[{Expense ID}], PRE_FILTER), 
	[{Appointment ID}], 
	PRE_FILTER
)
1 Like
5

Hi @David_Wong

yeah. That does the trick. Many thanks