Top N Products - different results in Pivot Table

Question & Answer
, ,
1

Calculated field topNProduct = rank([SUM({Sales SGD}) DESC], [Brand, year])

2 Pivot tables created with filter on topNProduct as below.

image
image982×373 56.7 KB

image
image216×271 16.4 KB

Different results of the 2 pivot tables when “month” is used as column. What is wrong?

2

Hi @lbl,
Normally when you add an additional type of aggregation, like adding ‘Month’, it can alter results.
Can you share what the results were and how they differed so that we can try and explore why there is a difference?

3

hi @Brett

Below is correctly showing Top 10 Products under each Brand.

image
image582×498 12.7 KB

For the Top 10 Products, I would like to see the breakdown by month and hence, “month” is added as column. However, the results is different.

image
image1200×286 13.4 KB

Thanks!

4

Hi @lbl,
What if you added ‘Month’ into your calculated field instead of ‘Year’ to account for the additional aggregation?
You could still setup a filter for the year that you’d like to limit to.

Let me know if that fixes the issue

5

Using topNProduct = rank([SUM({Sales SGD}) DESC], [Brand, month]), it is deriving the Top 10 in each month for each brand. Hence, this is definitely DIFFERENT from the original rank([SUM({Sales SGD}) DESC], [Brand, year]).

Any solution?

6

Hi @lbl,
Yes, I know that would be different, I was trying to see how the pivot table is aggregating the numbers as I’m unaware of the values you’re receiving since those have been removed.

Instead, why don’t you try using this calculated field for your TopNProduct filter:

rank([{Sales SGD} DESC], [{Brand}, {Year}], PRE_FILTER)

I tested this out on a similar pivot table layout and received the same results when using ‘Year’ and using both ‘Year’ and ‘Month’.

1 Like
7

hi @Brett,

rank([{Sales SGD} DESC], [{Brand}, {Year}], PRE_FILTER) does not work. :frowning:
It is not giving top N in each brand for both Year and Year/Month.

8

Hi @lbl,
What is it returning, and how is it differing? Here’s an Arena view of my testing sample where I was able to get them to match. Other than the different fields, let me know what you are doing differently that may be stopping you from replicating what I have:
Top N Products - different results in Pivot Table

Alternatively, I can’t really help out much further if you are unable to share results being received as I do not have the same dataset as you to test out. If you can create a copy of your analysis with anonymized data and upload to the Arena view, I may be able to assist further.

1 Like
9

Hi @lbl,
Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your initial post?

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

Thank you!

10

Hi @lbl,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!