Replicating Tableau Nested FIXED LOD in Quick Sight

Q&A
, ,
1

Hi Team,

Issue Summary: I am migrating a complex calculation from Tableau to QuickSight and am struggling to replicate the results of a nested FIXED LOD. The calculation involves a ratio where the denominator requires two levels of “fixed” aggregation: first finding a MAX at the Account level, then summing those results at a higher grouping level.

Tableau Logic:

{ FIXED [CDM],[Product Type] : SUM([qty]) }

/

{ FIXED [CDM],[Product Type], [Time Period Filter] : SUM({ FIXED [Account], [Product Type] : MAX([iol_consignment]) }) }

Product Type Calculation:

IF [qty] > 0 or [iol_consignment] > 0 then [modified_desc] END

Time Period Filter Calculation:

CASE [Time Period]

*WHEN 1 THEN*

    *\[dt_ic\] >= DATETRUNC('year', \[Max Dt Period\])*

    *AND \[dt_ic\] <= \[Max Dt Period\]*

*WHEN 2 THEN*

    *DATEPART('month', \[dt_ic\]) = DATEPART('month', \[Max Dt Period\])*

    *AND DATEPART('year', \[dt_ic\]) = DATEPART('year', \[Max Dt Period\])*

    *AND \[dt_ic\] <= \[Max Dt Period\]*

*WHEN 3 THEN*

    *DATEPART('month', \[dt_ic\]) = DATEPART('month', DATEADD('month', -1, \[Max Dt Period\]))*

    *AND DATEPART('year', \[dt_ic\]) = DATEPART('year', DATEADD('month', -1, \[Max Dt Period\]))*

*WHEN 4 THEN*  

  *\[dt_ic\] >= DATEADD('month', -11, \[Max Dt Period\])*  

  *AND \[dt_ic\] <= \[Max Dt Period\]*

*ELSE FALSE*

END

QuickSight Attempt -1:

sumOver(qty, [CDM, {Product Type}], PRE_FILTER)

/

sumOver(maxOver({iol_consignment}, [Account, {Product Type}], PRE_FILTER), [CDM, {Product Type}, {Time Period Filter}], PRE_FILTER)

QuickSight Attempt -2:
sumOver(qty, [CDM, {Product Type}], PRE_AGG)

/

sumOver(maxOver({iol_consignment}, [Account, {Product Type}], PRE_FILTER), [CDM, {Product Type}, {Time Period Filter}], PRE_AGG)

QuickSight Attempt -3:

Numerator:(getting closer values when taken for each Account)

sumOver(qty, [CDM, {Product Type}],PRE_FILTER)

QuickSight Attempt -4:

Denominator :

sumOver(max({iol_consignment}), [Account, {Product Type},CDM, {Time Period Filter}])

QuickSight Attempt -5:

Denominator 1

maxOver({iol_consignment1},[Account,{Product Type}],PRE_FILTER)

Denominator 2:

sumOver({Denominator 1},[Account, CDM,{Product Type}],PRE_FILTER)

Final Division:

min({Div Uti num})/min({Denominator 2})

Not able to replicate the exact nested LOD logic from Tableau in QuickSight, could someone guide me on the correct LAC-A nesting ?
WLS-Luis

2

Hi @Pavi.98 and welcome to the Quick Community!
It can always get tricky when dealing with nesting LAC-A functions in QS!
You may need to separate the denominator into two separate calc. fields to easier handle; what if you tried something like the following:

Max Calc.
maxOver({iol_consignment}, [Account, {Product Type}], PRE_FILTER)

Then incorporate that into your denominator:
sumOver({Max Calc.}, [CDM, {Product Type}, {Time Period Filter}], PRE_FILTER)

Let me know if this works for your scenario, if not accurate, maybe you could create an anonymized version of your analysis and upload to the Arena so that we can assist easier by testing out various functions

3

Hi @Brett,

I tried the same, but it didn’t work. I even tried to bring the max calc alone from dataset level, but sadly that also didn’t work.

4

Hi @Brett,

To make this easier to troubleshoot, I have created an anonymized version of the analysis and uploaded it to QuickSight Arena. You can interact with the calculated fields and the underlying data structure there to see exactly where my nested LOD is diverging from the expected Tableau results. Any guidance on the LAC-A nesting would be greatly appreciated!

Tableau Nested FIXED LOD

5

Hi @Pavi.98,
Another work around I would consider testing for this scenario would be to move one of your calculations to the dataset layer, that way you’re not having to handle to mismatched aggregations.
I’ll take a look at the arena view as well to try and test out other options

6

Hi @Pavi.98,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

7

Hi @Pavi.98 ,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you