1 2 Previous Next 16 Replies Latest reply on Jul 7, 2016 8:52 AM by Shinichiro Murakami

# USing LOD expression and group by

Attached is mock up sample.I have a month and number of executions report.I need chart of top5,top 10 ,top 15 percent of total executions represented in month chart with colours.

If we use LOD expression for Percent of total like

SUM([Number of Executions])

/

SUM({FIXED [DateMonth] : SUM([Number of Executions])})

How can we keep SUM([Number of Executions]) for only top 25 based on Number of Executions count. and use it to drop over month chart so it shows how much percent is it.

• ###### 1. Re: USing LOD expression and group by

Lavanya,

I'm sorry I'm confused with the number between absolute value and percentage.

Could you explain little bit more detail about what you need.

top 5 ,top 10 and top 15 is percentage with using what?  is that name or report ID?

And what do you mean by top25,  is this top 25 name or top 25% of name, or top 25 count of report ID, top25% of report ID?

Thanks,

Shin

• ###### 2. Re: USing LOD expression and group by

Thanks for looking,

Top5=Sum(Number of Executions) order by desc[upto 5 based on name column]

Top10=Sum(Number of Executions) order by desc[upto 10 based on name column] etc

Others=Rest of them

I need to represent best visualization chart to show Top 5,Top 10 executed reports in every  month and how much total percent they are among total executions in a given month.

• ###### 3. Re: USing LOD expression and group by

TOP N related VIZ is always tricky because it requires table calculation.

Anyways, Here is my approach.

The key is using Index and advanced table calculation setting to all the fields concerned.

You can use parameter if you will show this one by one, but I created two different graphs.

• Steps

Create Calculated field with combining two fields

[Name by Month]

[Name]+" "+[DateMonth]

// this field is used for sorting at all time in this case. *** each month sort order of "Name" is different ***

create calculated field of [index] as below

[Name Top 10]

if [Index] <=10 then attr([Name])

elseif [Index]=11 then "Other" END

Create calculated field

[Number of Executions Top10]

if [Index]<=10 then sum([Number of Executions])

elseif [Index]=11 then window_sum(sum([Number of Executions]),11,last())  END

Create calculated field for percentage calc.

[Top 10 Percentage]

if [Index]=1 then

window_sum(sum([Number of Executions]),0,10)/attr({fixed[DateMonth]:sum([Number of Executions])})

END

To put [Top 10 Percentage] label on the graph, create dual axis bar chart with synchronize the axis.

Do same(similar) thing for TOP 5.

Don't forget to change "10" related calculation to "5".

Thanks,

Shin

9.0 attached.

• ###### 4. Re: USing LOD expression and group by

Thanks for detailed explanation,no report names need to be shown,in the chart,top 25 group can be shown as Top25 with one color[this explains how much percentage top 25 occupied],Top50 with another color etc for a month  in one line bar in the chart.

• ###### 5. Re: USing LOD expression and group by

In this ,in place of names,what I need to show is as below in percent of totals

Top 10 30%

Top 5  20%

• ###### 6. Re: USing LOD expression and group by

Also

this formula

if [Index]<=10 then sum([Number of Executions])

elseif [Index]=11 then window_sum(sum([Number of Executions]),11,last())  END

is not giving total sum for 10

• ###### 7. Re: USing LOD expression and group by

[Percentage]

[Number of Executions]/{fixed [DateMonth]:sum([Number of Executions])}

Thanks,

Shin

• ###### 8. Re: USing LOD expression and group by

Shin

Thanks for detailed explanation.This is what exactly needed.

final question,why top15 and rest color not able to get in the same chart.I want each one color,Top10,Top15,Top25 etc different color etc.

• ###### 9. Re: USing LOD expression and group by

Shin

I saw your other post on Top N pie as below ,I am looking for same but instead of each customer,my case here is I need Top10 grouped into one ,Top20 grouped into one,etc and their percentage.We can represent in line chart as above or pie also looks fine.

• ###### 10. Re: USing LOD expression and group by

I assume you are using the 'set' approach that Shin provided....

In which case, as the set is created against customer (or customer + month) the set allocation of In/Out is returned at a row level, and so can be used to create a dimension.

So for Top 10/Rest it would be

IF [Top 10] THEN 'Top 10' ELSE 'Rest' END

btw [Top 10] here is just the set...they equate to True (in) or False (out) so no need for any further statements.

You can then bring this dimension onto colour and you'll get %age (of whatever measure) of Top 10 and the rest....you can equally just put the set onto the colour and change the set alias as Shin has showed.

I'm a little confused on your actual requirement, as won't the Top 20 include the Top 10?...however ignoring this you can do like

IF [Top 10] THEN 'Top 10'

ELSEIF [Top 20]  THEN 'Top 20'

ELSE 'Rest'

END

As an IF statement exits once a condition is found you'll actually get...

Top 10 - Top 10 customers

Top 20 - Customer 11 - 20

Rest - The rest

Hope that helps.

• ###### 11. Re: USing LOD expression and group by

Thank you for following up.

Shin

• ###### 12. Re: USing LOD expression and group by

No problem....I feel a bit like Gary Lineker* here, after your excellent and detailed explanation!

*for those not versed in the depressing affair that is our England soccer team...Gary Lineker was a 'goal-hanging' striker in the 1990s...the rest of the team did all the hard work, and then he'd pop up, knock the ball in the goal and take all the glory!!

• ###### 13. Re: USing LOD expression and group by

Shin/Simon

Sorry for the confusion.My issue here is only(one measure) Top10 is shown properly whreas all others are ignored in the chart.I am not able to show all on the chart.

Regarding this

I'm a little confused on your actual requirement, as won't the Top 20 include the Top 10

Yes,I want to show Top10,Top20 percentages separately in the chart.side by side chart also should be okay,or pie chart also should be fine.

For eg,for

May                                                                           June

Top10 percentage,Top20 percentage, etc               Top 10 %,Top20% etc.

• ###### 14. Re: USing LOD expression and group by

Hi Iavanya,

So I think we can achieve what you need using Running Sum....

So I've used the Top 5, 10, 15 sets (in Shin's example) to create a Dimension against each Customer

[Top Customer DIM]

IF [Top 5] THEN 'Top 5'

ELSEIF [Top10] THEN 'Top 10'

ELSEIF [Top15] THEN 'Top 15'

ELSE 'Rest'

END

As mentioned previously...The Top 5 = Customers 1-5, Top 10 = Customers 6-10, Top 15 = Customers 11-15...We can't 'create' rows in Tableau, and so can't count customers 1-10 in Top 5 & Top 10...but with Running Sum we can!!

In the attached I've shown both the RUNNING_SUM and the RUNNING_SUM %age of Total (as an aside I've also put in a Pareto Curve Tab...you might find this a useful way of looking at your data!)

So I bring [Top Customer DIM] and [DateMonth] into the Viz....and bring in SUM([Number of Executions])....then using the 'Quick Table Calc' I set to Running Total

and set the Compute Using to [Top Customer DIM]

This means the running_sum re-starts every month.

With Running_Sum you get an option to perform a secondary calculation (tick this box) and you can choose a secondary calculation....I set this to Percent of Total

this, btw is exactly the same for the Pareto curve.

Hope that makes sense.

2 of 2 people found this helpful
1 2 Previous Next