8 Replies Latest reply on Feb 24, 2016 5:58 AM by Simon Runc

# Calculation Help

Below is the extracted twbx file where i need to calculate the average for incoming volume and outgoing volume.

I have used a zn(lookup()) for both and after it when i try to find the average using grand total , the valule of grand total remains same.

I have been trying a lot but could not get enough resources online.

I am attaching the snapshot of the requirement.

Kindly have a look and suggest .

Regards,

Manas

• ###### 1. Re: Calculation Help

Can anyone please have a look ?

• ###### 2. Re: Calculation Help

hi Manas,

So grand totals in Tableau are actually calculated at the 'Grain' (or Level of Detail) one above the Viz..and as such you do get these situations when using Table Calcs (as the Table Calc for the Grand Total is being run at a different level of Detail from the main values)...Can I ask why you are using Lookup? (is it purely to populate the blanks with zeros?

If I remove the LOOKUP part...I get the following

Once I've switched the Calculate Totals to Average

One other reason you may have been using LOOKUP is that you want the NULLs to be zeros in the Average calculation? As you can see here the NULL values aren't included as zeros in the Average Calc (so for 25th Jan Incoming is 1.5 ((1+2)/2) and not 0.375 is we have evaluated the NULLs are zero.

Hope that helps.

• ###### 3. Re: Calculation Help

Hi Simon,

Yes, lookup is used only to populate blank values with 0 . (which was mandatory as per requirement). also , grand total has to be on row level. is it possible to find avg on row level and then populate the blanks with 0 ?

regards,

manas

• ###### 4. Re: Calculation Help

Yes i'm pretty sure we can do that...Just so I'm clear, would you want the average for the example I gave above to be 1.5 or 0.375? Depending how you want it calculated will  change the solution.

• ###### 5. Re: Calculation Help

Hi Simon,

I am attaching a screen shot which will make it clear . so i have picked start date as 1st dec, 2015 and end date is 6th dec, 2015. the data output (column pill DAY(day) shows all days except 5th dec , as there is no data on that day). lets now look at time of day (row pill) and lets focus on 10:01-11:00 . it has been divided into incoming volume and outgoing volume. so average of incoming volume will be 2+0+0+0+0 (row level ) divided by no. of days. similarly outgoing volume = 3+0+0+0 (row level) . similarly average for this will be calculated. and these averages should appear at row level for 10:01-11:00.

Hope this makes it clear.

Regards,

Manas

• ###### 6. Re: Calculation Help

Yes that makes sense...I thought it was the Column Grand Totals were we after!!

So the reason LOOKUP populated the NULLs (blanks) with Zeros is that it is a table calculation, and when we have 2 opposing blue pills and a table calc, Tableau envokes 'Densification'...This is a very complicated and not well documented feature (I'm not even sure it's deliberate!!). If you are interested Joe Mako has done some research into when/why it occurs and done this excellent video Tableau Request Live - Data Densification on Vimeo

As such I can keep the measures as Non-LookUps, and use an  index() calculated field (also a Table Calc) to trigger the densification.

As you can see from the below I have it working

However this is in Tableau 9.0, and when I try and replicate this in 8.2 (which is the file you sent through) it doesn't do the same thing (as I said I think densification is 'side-affect' and so as they change Tableau from version to version it looks like this has been affected). I can post the 9.0 workbook for you if that'll help?...but I'll have a bit more of a play with the 8.2 version and see if I can get it doing the same thing!

1 of 1 people found this helpful
• ###### 7. Re: Calculation Help

Nice work, appreciate it Simon.

Can you send me the 9.0 report and let me have a look at it.
If you could figure out a way in 8.2, it would be beautiful.

Regards,

Manas

• ###### 8. Re: Calculation Help

...welcome to the murky world of 'Data Densification'!!! - a very useful and frustrating feature, in equal measure!!

attached is the 9.0 version...I'll have a bit more of a play with the 8.2 version and let you know if/when I get to a solution!