Use Level Aware Calculations as a dimension

Question & Answer
,
1

Hello,

Is anyone aware of any hacks to use Level Aware Calculations as a dimension?
I want to replicate Tableau’s Fixed Functionality in QS, which I have using LAC. But am unable to futher use an ifelse condition on it to categorise it. The error I get is:

image
image974×91 6.08 KB

Happy to share more details 1:1. (Internal Amazon Only)

Thanks
Tanisha

2

Also, what is the right way to emulate FIXED in Quicksight and what is the difference between each of the following Quicksight Options below?
Tableau:
{ FIXED [reporting_period], [period_id],[vendor],[imdb_id_pv]: MIN([pv_rights])}

Quicksight Option 1:
min({pv_rights},[reporting_period, period_id, vendor, imdb_id_pv])

Quicksight Option 2:
min({pv_rights},[{reporting_period}, {period_id}, {vendor}, {imdb_id_pv}])

Quicksight Option 3:
minOver({pv_rights},[reporting_period, period_id, vendor, imdb_id_pv])

Quicksight Option 4:
minOver({pv_rights},[{reporting_period}, {period_id}, {vendor}, {imdb_id_pv}])

Thanks,
Tanisha

4

Hi,

Here is a recent release explaining level aware calculations : https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Calculation-Level-Aware-Calculations

Regards,
Koushik

5

Hey Koushik,

Thanks - I have gone through the video on Democentral and found the right way to use LAC-A. The trouble starts when i further use these fields as flags in an ifelse construct and visualise it in a pivot table. That’s when I get the error: LAC-A expressions cannot be used as dimensions.

Let me know if you know any workarounds for my use case, I can reach out 1:1.

Thanks,
Tanisha

6

Hi Tanisha,

Could you post here an example with dummy data and the expected result ?

Regards,
Koushik

7

Hi Koushik,

Sure, let me try to elaborate on the problem I’m trying to solve
I’m using LAC-A to build the following flags:

Min_PV_Offer : min({pv_offer},[reporting_period, period_id, vendor, imdb_id_pv])
Min_PV_rights : min({pv_rights},[reporting_period, period_id, vendor, imdb_id_pv])
Min_PV_avail : min({pv_avail},[reporting_period, period_id, vendor, imdb_id_pv])
Max_PV_Offer : max({pv_offer},[reporting_period, period_id, vendor, imdb_id_pv])
Max_PV_rights : max({pv_rights},[reporting_period, period_id, vendor, imdb_id_pv])
Max_PV_avail : max({pv_avail},[reporting_period, period_id, vendor, imdb_id_pv])
Max Future Avail : max(ifelse({is_future_release (c)} = ‘Y’,1,0),[{reporting_period}, {period_id}, vendor, {imdb_id_pv}])
Max PV Missing Type : max(ifelse({PV Missing (Type)}=‘B’,1,0),[{reporting_period}, {period_id}, vendor, {imdb_id_pv}])

The following logic then uses these flags to calculate the Availability Status which is calculated below:

ifelse(({Min_PV_Offer}=1 AND {Max Future Avail}=0), ‘Complete’,
(({Max_PV_Offer}=0 AND {Min_PV_rights}=1) OR ({Max_PV_Offer}=1 AND {Min_PV_rights}=1) OR ({Min_PV_rights}=1 AND {Max Future Avail}=1)),‘Backlog’,
({Max_PV_avail }=1 AND {Max PV Missing Type} = 1), ‘Backlog/Unavailed’,
({Max_PV_Offer} =1 AND {Min_PV_rights}=0 AND {Min_PV_avail}=0), ‘Complete/Unavailed’,
{Max_PV_avail }=0, ‘Unavailed’,
‘Backlog/Unavailed’)

When I try visualizing the Availability Status in a pivot table while creating the flags using LAC-A: I get the error that I cannot use LAC-A as a dimension. What I’m expecting is output in this form (refer column PV-Availability status_LAC-W)
image

Currently, the column has the wrong categorization as I tried using LAC-W to calculate the flags . Instead, I want to use LAC-A to calculate theflags and then build Availability Status using the ifelse construct and be able to visualize it in a pivot table in a similar format.

Please let me know what I’m doing wrong here.

Thanks,
Tanisha

8

Hi,

At this point since LAC-A is not supported as dimensions, the categorization is not possible.
That would mean the other option would be to test implementing it using LAC-W .

When you say the categorization is wrong after you tried LAC-W , could you provide an example by attaching dummy dataset here ? ( an example of what I mean with dummy data Question about creating a calculated field using if else and custom aggregation ) . For eg in your screenshot , Unique Title (The Boys-… ) : you are classifying it into ( PV-Availability status_LAC-W ) . What is the correct status ?

9

Hi Koushik,

I think it looks accurate. The categorization does match the intended values.
I’ve used LAC-W in the below format similarly for all flags, please call out if something looks odd.

Max_PV_avail:
maxOver({pv_avail},[{reporting_period}, {period_id}, vendor, {imdb_id_pv}], PRE_FILTER)

Also while comparing overall values, LAC-A v/s LAC-W, I find the values to be a close match after updating relevant filters.
image

Thank you :slight_smile: I will update this thread - should I find anything odd upon further deep dives.

Regards,
Tanisha

10

Hi, @Tanisha_Shetty. We hope that Koushik’s solution worked for you. I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks!

11

Hi Kristin,

Yes, for now. If I face additional challenges in the future, I’ll update this thread :slight_smile: Thanks!

1 Like
12

Glad to hear it @Tanisha_Shetty!