Count of ASIN bringing top 80% of sales

Question & Answer
, , ,
1

Hi, I am trying to create a visual with distinct count of ASIN for the top 80% sales within a shelf. It seems calculating the top 80% sales has to include the {asin} in the partition, but when I create the visual, I want to see it at the shelf level, but QuickSight does not allow me to do that. I wonder how I can solve this problem? Essentially I just want to calculate 4/7 at the shelf level.

ASIN count at shelf level
ASIN count at shelf level1621×778 87.3 KB

2

Hi @jimmix

Can you share the details of your calculated field to review and also show the fields selected for the visual that has the errors?

Regards,
Giri

3

Hi Giri, here are the calculated fields definitions:

  • running_sum: runningSum(sum(Sales)/sumOver(sum(Sales),[Vertical, Category, Shelf]),[sum(Sales) DESC],[Vertical, Category, Shelf])
  • shelf_total: sumOver(sum(Sales), [Vertical, Category, Shelf])
  • # Top 80% ASIN: sumOver(ifelse(maxOver({running_sum}, [Vertical, Category, Shelf, ASIN]) <= 0.8 and maxOver({running_sum}, [Vertical, Category, Shelf, ASIN]) >= 0, 1, NULL), [Vertical, Category, Shelf])
  • Shelf Level ASIN Count: sumOver(ifelse(maxOver(sum(Sales), [Vertical, Category, Shelf, ASIN]) > 0, 1, NULL), [Vertical, Category, Shelf])

As for the failed visual, it is essentially removing the ASIN dimension from the field well like shown below:

ASIN count at shelf level failed
ASIN count at shelf level failed1420×697 82.4 KB

1 Like
4

Hi @jimmix

What if you bring in the ASIN field into the dimension and then hide it? Does it work?

Regards,
Giri

5

Hi Giri,

That wouldn’t work because this would just leave duplicate rows at the shelf level (hiding the ASIN field does not make the table distinct)

Kind regards,

Jimmy

6

Hi @jimmix,
It’s been awhile since last communication on this thread, were you able to find a work around or are you still in need of further assistance?

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

Thank you!

7

Hi Brett, I still couldn’t find a work around, so some further assistance would be much appreciated.

Kind regards,

Jimmy

8

Hi @jimmix,
With the number of calculated fields you’re working with, this may be a lot easier to assist further if you were to create a duplicate of your analysis and upload to the Arena view.

9

Sure, here is the link of the dashboard in the Arena view: Top 80% ASIN count at Shelf Level

10

Hi @jimmix,
Thank you for creating an Arena view for me to look in to this further. After testing out various different scenarios though, I don’t believe this will be achievable due to the runningSum calculation creating too much nesting of aggregations.
Since you’re using a LAC-W calc. on the running sum with ‘POST_AGG_FILTER’ (default if you do not use ‘PRE AGG’ or ‘PRE FILTER’) as your calculation level, using ASIN in the partition level as well will require it to be in your visual.

If there was a way around to not use the RunningSum calculation as it’s currently setup, there could be a work around but unfortunately, I don’t believe this will be possible with the current calculation nesting in place.