Aggregations when a metric includes Multiplication/division

Q&A
,
1

Hi,

I am trying to calculate a field Incremental Sales

Which is

Incremental Sales = Mailed Sales - Expected Sales

We have two groups Mailed and Control

Mailed sales is calculated as the sum of revenue of mailed converted customers .

Expected Sales = Expected Number of conversions * Control Average Basket Value

Expected Number of conversions = ifelse(mailed count campaign level)=0, 0, mailed count campaign level * min (control conversion rate campaign level )

This is calculated on a campaign level.

If there are no control customers for that campaign, the expected sales should be 0,

which should give

Incremental sales = Mailed sales

I am able to do this in a pivot table but not in KPI. Here is my prev post on this. I have updated the emtrics in the demo central .

https://community.amazonquicksight.com/t/aggregations-on-kpi/50153/16

2

Hi @liya101,

Hope this message finds you well!

After looking at this and your previous post for a bit, I am not sure if it is possible to achieve the same number as the pivot table, as on my end when I try to create a new calculated field for it, it raises various aggregation errors. My recommendation would be to see if you can somehow calculate the number (via something like a sumOver function) so you can do pre-aggregation and hopefully raise the same figure.

If that does not work for you as well, I would recommend trying to use another visual to display similar information. Let me know if this helps!

3

Hi ,

Thanks for your response. I tried using sumover. I need incremental sales as a topline KPI . If I am using sum over , it would give me an error since I need the sum of all campaign level incremental sales.

4

Hi @liya101,

Thanks for trying that suggestion out. Based on that occurrence, I believe using the topline KPI in conjunction with a campaign grouped fields is not currently possible. I know you mentioned that you need it as a topline KPI, but for it to display the correct figures, I think the only solution would be to try an alternative visual.

I would also recommend creating a support ticket with AWS Support, just in case there is some other reason as to why this is not working or provide other workarounds.

Hope this helps!

5

Hi @liya101

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

Hi @liya101

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!