11 Replies Latest reply on Jul 5, 2017 11:34 PM by praveen p

# Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hi All,

I have a visualization that is ranking a list of Top N partners based on sales, with a bar graph symbolizing month/month growth or year/year growth. I am using a Quick Table Calculation for % growth, and a combination of groups and parameters to specify Top N versus Others.

I am unsure how to calculate the % growth of the "Others" bucket. I am able to get the total sales for "Others", but not for the % growth.

1. For example, #11 is not referencing the "Others" bucket, but rather the 11th Merchant, AAF, and therefore the % growth is incorrect.

2. Also, I cannot figure out how to get the Mark Labels to show...

"Others" Dimension:

IF Rank(sum([Sales])) <= [Top N]

THEN MIN([Merchant])

ELSE "Others" END

"Others" Sales

IF RANK(SUM([Sales])) > [Top N]

THEN SUM([Sales])

END

Sales or sum of "others" sales

IF RANK(SUM([Sales])) <= [Top N]

THEN SUM([Sales])

ELSE WINDOW_SUM([Others Sales])

END

• ###### 1. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hoping someone can help soon..

• ###### 2. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hello ALEC,

A question.

Alec Chen wrote:

I have a visualization that is ranking a list of Top N partners based on sales

As for "sales", the sales for all the years or the sales for the latest year?

Regards

Lei

• ###### 3. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hi Lei,

I am looking for the latest (current) year.

Thanks,

Alec

• ###### 4. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hello ALEC,

Please refer to the attached workbook.

Please pay attention that, "Others' participates in the ranking.

Regards

Lei

• ###### 5. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hi Lei,

This solved my issue, thanks for your help!

• ###### 6. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hi Lei,

Thanks for your help in making this work. Currently the measurement is for the entire year, I would like to incorporate the parameters to have a view based on month-to-date versus LY month-to-date and year-to-date versus LY year-to-date,.

I created a field for MTD, which is functioning as expected

{ FIXED [Merchant]:SUM(IIF(int(str([Current Year])+[Current Month])=[Month454Skey Calculation],[Sales],0))}

I expect LY MTD to be similar, but it is not working:

{ FIXED [Merchant]:SUM(IIF(int(str([Current Year])+[Current Month])=[Month454Skey Calculation],[Sales],0))}

Based on this, I would expect YTD to be this, but it is not working either.

{ FIXED [Merchant]:SUM(IIF(int(str([Current Year])+[Current Month])<=[Month454Skey Calculation],[Sales],0))}

Do you have any ideas?

• ###### 7. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Hi Alec,

please try the below calculation for SUM Sales MTD LY and it will display correct results

{ FIXED  [Merchant]:SUM(IIF(int(str([Current Year]-1)+[Current Month])=

(INT(STR([FY])+[FY Month Number Calculation])),[Sales],0))}

1 of 1 people found this helpful
• ###### 8. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Thank you Praveen, that was a much simpler approach than mine. I'm still having trouble getting year-to-date to work. Does [FY] needs to be FIXED as well? Thanks for all your help.

• ###### 9. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Please try the below calculation for YTD

YTD :

{FIXED [Merchant]:SUM(IIF(([Current Year])=([FY])

and ([FY Month Number Calculation])>=([Current Month]) and [Current Month]

<='11',[Sales],0))}

1 of 1 people found this helpful
• ###### 10. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

Thank you again praveen. I modified the formula by switching the >= condition to <=, and it seems to have worked. I have a question on why the condition for [Current Month] <= '11'? Should it not be '12' to include the last month of the year?

• ###### 11. Re: Using a Table Calculation (YOY Growth) with Top N / Others Groups

It should include 12 as well Alec as per the [FY Month Number Calculation] calculation Jan was assigned 11 hence compared it with 11 please change the code to 12 or any number as per the requirement.

1 of 1 people found this helpful