Nested sum calculations in Quick Sight

Q&A
, ,
1

Hi all,

I have below nested SUM aggregation at certain dimensions level in Qlik. So, the summing is done once the inner calculation of [(distinct_count(debit)-distinct_count(credit))*multiplier] is first evaluated against multiple IF conditions. So the multiplier changes as per the IF condition. Please check the below full calculation from Qlik.

How can I replicate same in Quicksight? I can’t use nested distinct_countIf() function because it accepts only 2 params - and <statement_to_execute> if evaluates to TRUE. There is no param for FALSE evaluation (unlike ifelse() function). Also, I cannot use distinct_count() function in nested ifelse() conditions. It gives mismatched aggregation level error.

PLEASE SUGGEST alternatives for this calculation.

=SUM(aggr(If([IJ Account] = '3665116' AND [IJ Brand] = 'BMW', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 500,
          if([IJ Calendar Year] >= 2024 AND [IJ Calendar Month] >= 1 AND[IJ Account] = '3665116' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 300,
          if([IJ Calendar Year] <= 2024 AND [IJ Calendar Month] < 9 AND[IJ Account] = '3665116' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 200,
          if([IJ Account] = '3665117' AND [IJ Brand] = 'BMW', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 500,
          if([IJ Calendar Year] >= 2024 AND [IJ Calendar Month] >= 1 AND[IJ Account] = '3665117' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 300,
          if([IJ Calendar Year] <= 2024 AND [IJ Calendar Month] < 9 AND[IJ Account] = '3665117' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 200,
          if([IJ Calendar Year] < 2024 AND [IJ Account] = '3805006' AND [IJ Brand] = 'BMW', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 320,
          if([IJ Calendar Year] > 2023 AND [IJ Account] = '3805006' AND [IJ Brand] = 'BMW' AND [IJ FUEL TYPE_1] = 'BEV', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 370,
          if([IJ Calendar Year] > 2023 AND [IJ Account] = '3805006' AND [IJ Brand] = 'BMW' AND [IJ FUEL TYPE_1] = 'Non-BEV', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 320,    
          if([IJ Calendar Year] >= 2024 AND [IJ Calendar Month] >= 1 AND[IJ Account] = '3805006' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 300,
          if([IJ Calendar Year] <= 2024 AND [IJ Calendar Month] < 9 AND[IJ Account] = '3805006' AND [IJ Brand] = 'MINI', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 125,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0402', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 75,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0414', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 100,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0415', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 50,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0422', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 25,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0430', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 50,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0432', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 100,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0434', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 100,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0450', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 75,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '0451', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 100,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '4002', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 50,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '4004', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 50,
          if([IJ Account] = '3665018' AND [IJ Maco Code] = '4006', (Count(distinct [IJ Debits]) - Count(distinct [IJ Credits])) * 50))))))))))))))))))))))),
          
       [IJ Calendar Year], [IJ Calendar Month], [IJ Account], [IJ Brand],[IJ FUEL TYPE_1],[IJ Maco Code]))

BELOW calculation NOT WORKING in Quicksight..

image
image1207×132 6.91 KB

2

Hi @Murali_Kembhavi,

Have you tested out using the distinctCountOver function instead as that allows you to specify the calculation level and may help alleviate the aggregation errors:

Additionally, here is some additional documentation that helps explain Quick Sight’s level aware calculations a bit more in depth

Let me know if you have any additional questions or if this helps with your case

3

Hi @Murali_Kembhavi,

Following up here as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post or were you able to find a work around in the interim?

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

Thank you!

4

Hi,

Using distinctCount_if() with additions for different conditions worked for me. You may close this thread. Thanks!

1 Like