1 2 Previous Next 22 Replies Latest reply on Oct 2, 2018 8:41 AM by Joe Oppelt

Calculations on several levels

I have a certain number of books, all categorized by genre. I keep data of how many hours I have spent reading each book. If I display the hours for every book read, it will show each hour. But if I display just the genres, I only see the total hours for each genre. This is fine but if I made a calculation to split up hours on a lower level, things don’t apply the same on the higher level. Also If I am displaying the hours spent on each book, how would I also show total hours (discrete) on the chart?

• 1. Re: Calculations on several levels

You can use a FIXED LOD to grab totals, and those values stay intact, even if you filter out parts of the overall data set.  THat's what FIXED LODs do, among other thing.

Alternatively, you can bring in everything to your underlying table, and use table calcs to filter what gets displayed.  A table calc filter doesn't delete rows from the underlying table.  It just controls what part of the table gets displayed.  Then you still have access to the total amounts and can do percentage of total calcs on individual books.

• 2. Re: Calculations on several levels

Hey Joe thanks for the response! Could help me out with this?

I have a formula that sorts out the hours at the level of each book depending on if it was read at night or not, but I have books with the same name in other genres. So, the fixed lod is forcing me to sum the value of these books with the same name. Essentially I need to lock the values of the lowest level of detail so that when I display higher levels, the formula I used at the lower level still has the hours split.

The issue is that my formula for calculating whether it was night or day comes from another data source and I use the isnull function to separate them since some of the hours have null for “daytime.” But for the genres, they all have daytime so it does not separate them anymore. Also the fixed fod is adding up hours for books with the same names.

• 3. Re: Calculations on several levels

At some point I'm going to need a sample workbook to keep all the details straight.

You can string as many dimensions as you want in the FIXED LOD (or any LOD, for that matter).  So include [Title], [Genre] (and maybe other dimensions) in the LOD.  Then if you have [Genre]on your sheet, you'll get separate values for the books with the same title in different genres.

• 4. Re: Calculations on several levels

But what if I want to plot a graph of the hours for only a few genres filtered out, with the night and day hours separated? The fixed LOD keeps adding up hours from other books with the same title.

• 5. Re: Calculations on several levels

The issue is I have a formula that separates the hours at the book title level, and I would like to create a graph for the genre level. How do I fix my formula to separate at the title level while displaying the values at the genre level? If I include title in the display they are separated, but as soon as I remove it The formula is applied at the genre level which does not properly separate it.

• 6. Re: Calculations on several levels

Can you hack up a sample workbook of what you have?  I'm having a hard time juggling all the details without a concrete example to play with.

If you have concerns about proprietary data, check out the video at this link:

I don't need your whole data set.  Just enough rows to demonstrate the various concerns you have to deal with.

• 7. Re: Calculations on several levels

Hey Joe, here is my workbook. I was using another example so that it was easier to explain.

I have the AFA unit tab displaying the actual hours and target time at the operation level. The AFA Chart tab next to it display the same information at the order number level.

I need to fix the Actual hours Red and Actual Hours Blue measures to the operation level so that I can display it at the order number level.

For example: Order number 10623172 (batch number AFZ0098) has both operations with red and blue hours. But on the chart it only displays red because the measure is being applied at too high of a level.

• 8. Re: Calculations on several levels

Hey Joe, did you get a chance to review my data?

• 9. Re: Calculations on several levels

Daniel -- Thanks for waking me up.

No, I never saw the update, so I totally missed it.

So we have a problem.

You uploaded a .TWB workbook.  We need a TWBX (which is a packaged workbook.)

Also, it looks like some of your data sources are on a server.  I get this message:

You'll need to make a local copy so that the data gets stored in the TWBX.  Check out this link, especially the section for creating a workbook with Server data sources:

• 10. Re: Calculations on several levels

Okay this should be accessible, let me know if there are any other issues!

• 11. Re: Calculations on several levels

(V 10.4 here)

Yup.  I can open it.

I'll look at this shortly.

• 12. Re: Calculations on several levels

OK.  I'm looking at this.  Actually you are getting the blue number on the AFA Chart sheet.  (The color legend is set backward.)

the RED calc results in NULL because there is no "else" logic (so tableau default to NULL for "else" in that case).  You get to the ELSE because

SUM([Sheet1 (Anon target)].[Target Time]) is not null.  Because this is at the blending level of Material AND Operation, you have two numbers coming back from the secondary source, and on the AFA CHart sheet we are only evaluating at the level of Material, so those two numbers get added together.  And yes, one of the two is null, but the other isn't, so the sum is not null.

If you want both secondary numbers evaluated separately, you'll need to have Operation on that sheet (which, of course, changes the nature of what you are looking at on that sheet.)

What do you actually expect to happen on this sheet?

• 13. Re: Calculations on several levels

I want the formula that split them up to apply on the operation level but display on the material level. The end goal is to stack the two values so that you can see the total of actual hours, and you can see how much of the actual hours is red and how much is blue. Then have that compared to the target time. Is this possible in Tableau?

• 14. Re: Calculations on several levels

I'm struggling to understand what you hope to see.

For now, in the attached, I added Operation Number on the AFA Chart sheet.  And I filtered the two sheets to look only at Order = 10623172

The result is essentially the same as AFA Unit, only running in a different direction.  You have 5 different values displayed on the Chart sheet.  Two target values, two Blue values, and one Red value.  What should be the actual displayed values, and what should be the shape of the actual viz that you hope to see here.

1 2 Previous Next