14 Replies Latest reply on Oct 11, 2016 12:37 PM by Vamsi Immadisetty

# Calculate highest Version average

Hi I wanted to calculate Maximum version average. Let say my date is like this.

RID                VERSION

1434577           4.323.32

1434578           4.9.67

1434579           4.323.32

1434580           5.6.435

1434581           6.1.2.99

1434582           6.1.2.99

1434583           6.1.2.99

1434584           6.1.2.99

1434585           6.1.2.99

1434586           6.1.2.99

1434587           6.1.2.100

1434588           6.1.2.103

1434589           6.1.2.103

1434590           6.1.2.103

Version 6.1.2.99 repeated more number of times. I am expecting to display on sheet as 6.1.2.99  -  42.85%  . I don't want to display rest of the versions and their percentages.

• ###### 1. Re: Calculate highest Version average

If you're on V9.0 or higher, do a LOD to get the total count of rows.

Then do a window_max(count([Version]) to get the biggest COUNT.  Divide that value by the initial count you did in the first calc, and you have the percent.

If you need help pulling this together, create an excel file of the data, give it a shot, and upload the packaged workbook showing where you get stuck.  (Be sure to tell us the version of Tableau you are using.)

• ###### 2. Re: Calculate highest Version average

HI Vamsi,

Like Joe wrote, LOD is the best way to accomplish this.  The formula below will give the percentage you are looking for.

Regards,

Ivan

SUM ( IF { FIXED : MAX([Version]) } = [Version] THEN 1 ELSE 0 END)/SUM({ FIXED : SUM([Number of Records])}) • ###### 3. Re: Calculate highest Version average

Ivan is also correct but shows all versions.

See this one.

• ###### 4. Re: Calculate highest Version average

Hi, thank you very much for your reply. It worked like a charm. JOE, IVAN and LUCIANO saved my time.

• ###### 5. Re: Calculate highest Version average

If I add any other dimension as filter, will these functions work or I have to include dimension in function itself

• ###### 6. Re: Calculate highest Version average

It depends.

Do you want the percentage as a function of all the rows in the data source?  Or just a function of the filtered set of data?

The denominator:

SUM({ FIXED : SUM([Number of Records])})

will get all the rows in the data source.

• ###### 7. Re: Calculate highest Version average

let say I have one more dimension called Atype(Global, Segment). Some of these versions fall under Global and some are under Segment. If wanted to see Max version in Global and its percentage , Max version in Segment and its percentage.

Either I should be able to add Atype in filter or have to calculate in separate calculations.

• ###### 8. Re: Calculate highest Version average

It's time to upload a sample packaged workbook so that we can be talking about a concrete example.  I'm struggling to juggle all the factors involved now.

• ###### 9. Re: Calculate highest Version average

Hi Joe, Attached is the data deck and calculation.

• ###### 10. Re: Calculate highest Version average

See attached.

Sheet 1 is your data with percentages by Version.  Calculation2 does the calc offered by Luciano.

Sheet 2 is just the max version displayed.

Sheet 3 has added Atype.  Calculation3 modified Calc2  to take [Atype] into consideration, but we're still dividing by the total sum of rows in the data source.  (So all the individual calcs on this sheet add up to 1.00.)

Sheet 4 has added [Atype] in to the denominator (in Calculation 4) so that you get percentages by [Atype].

Sheet 5 modified the filter to look at Calculation 4 instead of calculation 2, and grabs the largest for each [Atype].

• ###### 11. Re: Calculate highest Version average

I did this workbook in V9.0 so that whatever version you are using will still be able to read this.  (Unless you are on 8.x, in which case all this needs to be redone because LODs weren't in 8.x.)

• ###### 12. Re: Calculate highest Version average

Hi Joe, million thanks for your reply. I have one last clarification what if I wanted to get the latest version by Atype. Like

Segment          6.2.342               26%

Global              6.3.51                 20%

I tried apply max function in LOD function but not getting exact result as I am expecting. I am using the Tableau Version 10.0.0

• ###### 13. Re: Calculate highest Version average

Sheet 6 is a duplicate of sheet 4.  I created a calc that just increments whenever there is a new Version down the chart.  I placed it on TEXT so you can see how it works.  I did TABLE(down) on that calc so that it evaluates in the proper direction.

Sheet 7 is a duplicate of 6.  I added a calc that I will eventually use as a filter.  This calc looks at the prior calc and also looks to see if the current version is NULL.  When I get to the MAX [Running Count], and if the value is not null, then set the calc to 1.  You can see in the display that it gets set to 1 for the highest [Running Count] that is not null.  I also set this to TABLE(DOWN).

Sheet 8 is a duplicate of 7.  I took [Running Count] off the TEXT shelf.  Ditto [Get Max...].  I dragged Get Max to the filter shelf and told it to select only for value = 1.  Now you just get the two rows where that value is 1.

1 of 1 people found this helpful
• ###### 14. Re: Calculate highest Version average

Hi Joe, thank you very much for step by step procedure. It is flawless explanation and you saved my day.  I did little modification to the code.

Step 1 :  Separated both AType in two dimensions IF [AType]='Global' THEN [Version] ELASE '0' END

Step 2 : I captured the max version in two different dimensions crated in Step 1 (for both segments)

• {FIXED [AType] : MAX([ATypeVersions])}

Step 3 :  Calculate average for Highest version by segment in different dimension using IF condition

COUNT(IF [Version]=[GlobalLatestVersion] THEN 1 END)/SUM([Number of Records])

Finally created two different sheets for Global and Segment and added them in dashboard.