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

    USing LOD expression and group by

    lavanya koneru


      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
          Shinichiro Murakami

          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
            lavanya koneru

            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
              Shinichiro Murakami

              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
                lavanya koneru

                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
                  lavanya koneru

                  Top.png
                  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
                    lavanya koneru

                    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
                      Shinichiro Murakami

                      [Percentage]

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

                       

                       

                       

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: USing LOD expression and group by
                        lavanya koneru

                        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.

                         

                        Top.png

                        • 9. Re: USing LOD expression and group by
                          lavanya koneru

                          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.

                           

                          Top.png

                          • 10. Re: USing LOD expression and group by
                            Simon Runc

                            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
                              Shinichiro Murakami

                              Simon Runc

                              Thank you for following up.

                               

                              Shin

                              • 12. Re: USing LOD expression and group by
                                Simon Runc

                                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
                                  lavanya koneru

                                  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
                                    Simon Runc

                                    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