Advanced calculations involving duplicates

Q&A
, ,
1

Hi,

Trying to calculate %s based on “Sum of WEIGHT field for unique ResponseId”. Here dataset contains duplicate ResponseId though each ResponseId has a single WEIGHT factor. Dataset somewhat looks like this.

ResponseId WEIGHT Q2
1 1.2 Option A
1 1.2 Option B
2 0.9 Option A
2 0.9 Option B
2 0.9 Option C
3 1.3 Option B
3 1.3 Option D

Now what I need is sum of weights for each option for which I have used calculated field - sum(max(WEIGHT,[ResponseId])) and getting the desired sum however I am unable to get to the correct Total sum of weights which should be 1.2 + 0.9 + 1.3 = 3.4, I need help to figure it out.

Sum of weights Total (sum of weights) % calc
Option A 2.1 3.4 61.76%
Option B 3.4 3.4 100.00%
Option C 0.9 3.4 26.47%
Option D 1.3 3.4 38.24%

Pls refer 2nd table “Q2. Attributes by Quarter” here

TEST 2 (Dashboard)

2

Hi @jitendral.yadav

To achieve your expected results, the dimension fields play a key role in the calculation. The Total (sum of weights) is based on unique values from the Weight field aggregated by the ResponseId field, which should total 3.4.

The calculation results look correct when aggregated by the ResponseId field alone. When you introduce an additional dimension, such as the Q2 field, the calculations dynamically adjust based on this new grouping.

Example:

UniqueWeight - sum(max(WEIGHT, [ResponseId]))
totalUniqueWeight - sumOver(sum(max(WEIGHT, [ResponseId])),[],POST_AGG_FILTER)
% Calc - UniqueWeight / totalUniqueWeight

image
image583×368 12.2 KB

3

Hi @Xclipse ,

Exactly, I don’t want the totalUniqueWeight to be affected by any other field. Is there a way to achieve it?

4

Hi @jitendral.yadav

Quick Suite calculations dynamically adjust based on the dimension fields included in the visual. Currently, it is not possible to create a calculation where the totalUniqueWeight remains completely unaffected by any other fields or filters within the same visual.

Quick Suite’s calculation engine evaluates aggregations relative to the visual’s dimensions, so totals will change as dimensions are added or removed.

If a fixed total is needed, a common approach is to create a separate KPI or summary visual that shows the total sums independent of other dimensions.

5

Hi @Xclipse

Thanks for reply.

I need this fixed total to calculate the %s so creating a separate visual wont solve my purpose.

6

Hi @jitendral.yadav

To achieve a fixed total that does not change with additional dimensions, you will need to preprocess the data to ensure the unique total is calculated at the source level based on the ResponseId. Quick Suite calculations are designed to dynamically adjust based on the dimensions present in the visual.

When you aggregate by ResponseId alone, the calculation correctly returns the expected total. However, as soon as you add another dimension like Q2, Quick Suite recalculates aggregations dynamically according to the new grouping, which leads to totals adjusting accordingly.

1 Like