Visual agg functions can't be used operands of as Level Aware Calculation (LAC) agg functions

Question & Answer
,
1

I have two calculated fields like below

PA = max({PA USD}, [{ExID}])
FA = max({FA USD}, [{ExID}])

And now I want to sum the above calculated fields per appointment with this formula ,
sum({pda approved test},[{Appointment ID}]) - sum({fda approved test},[{Appointment ID}]).
But I get the below error.
“Visual agg functions can’t be used operands of as Level Aware Calculation (LAC) agg functions”

ExId is a level down from appointment. So for one appointment I may have several ExID.
The end goal is that I want to have in a barchart per month the sum of PA - sum of FA but per appointment

The problem is that if I have the appointment in the visual the subtraction SUM¶ -SUM(FA) works correctly. But if I remove the appointment it takes all the sum while I want the sum when both PA and FA have values per appointment.

image
image474×301 16.3 KB

1 Like
2

Hi @pantelis,

Is PA the same as “pa approved test” and FA the same as “fda approved test”? From your description, they appear to be the same but I got a bit confused because your sums are referencing a different field name.

1 Like
3

Hi David, sorry you are right. Typo error. They are the same. So the error comes up when I try to do sum({PA},[{Appointment ID}]) - sum({FA},[{Appointment ID}]).
The issue is because of the granularity. I have one appointment that is linked to many ExID and one ExID is linked to many request_ID . So when I just sum I get multiple times the amount of ExID. That is why I have used max({PA USD}, [{ExID}]). To get it once. But then I get this error when I try to find the total difference between PA - FA per appointment ID level.

1 Like
4

Hi @pantelis,

You’re getting this error because you’re trying to nest LAC-A functions but that’s not allowed in QuickSight.

Can you try changing your calculated fields for PA and FA to use maxOver instead of max?

PA = maxOver({PA USD}, [{ExID}], PRE_AGG)

FA = maxOver({FA USD}, [{ExID}], PRE_AGG)

Then change your other calculated field to the following:

sum(max({PA}, [{Appointment ID}])) - sum(max({FA}, [{Appointment ID}]))
1 Like
5

Unfortunately this doesn’t achieve what I am trying to do. I want to find the difference per month of PA test and FA test but only when both PA test and FA test have values
So in the below image I want just to have the difference in 62267 and 62258 because these are the only rows that both PA and FA have values. So this works fine.

image
image667×282 21.8 KB

But when I pull out the appointment from the visual it sums all values per month regardless if there are nulls in PA test or FA test

image
image603×288 27.3 KB

I tried with the below function but nothing happened. Empty result.
sumIf({PA test}, isNotNull({PA test}) AND isNotNull({FA test}))

How can I have the expected result?

1 Like
6

Hi @pantelis,

Would you be able to use Arena to share some sample data? Just a few appointment IDs per month will do.

7

Hi @pantelis,
It’s been awhile since we last heard from you, are you still in need of assistance or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

8

Hi @pantelis,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!