5 Replies Latest reply on Feb 28, 2012 5:22 AM by Neal Smoller

# return max value for a day, for each month

There is no knowledgebase or forum answer for this:

i own a pharmacy.  we have 1000s of products, each dispensed at different quantities of doses on different days.  i'd like a table that returns:

month 1    month 2    month 3

drug name   max value over all days for each month

I have had difficulty finding this answer even via general excel, access, and sql help.

attached is a screenshot of the closest i can get.

i'd imagine this would require some sort of join...

• ###### 1. Re: return max value for a day, for each month

Hi Neal,

I think to get the desired outcome, a table calculation should be used. Mostly likely using total(max([Value field]))

Then, you will want to make sure to compute this calculation using Pane(Across) or by Month. The following link provides further information on table calculations:

http://onlinehelp.tableausoftware.com/v6.0/online/Output/Tableau%20Online%20Help-25-18.html

Hope this helps!

-Tracy

• ###### 2. Re: return max value for a day, for each month

Assuming that you wanted to show sum of doses from a day in a month when the largest total quantity was dispensed (i.e. maximum daily sum of doses), instead of a maximum singe dose, then the attached sample workbook does that using table calculation, as suggested by tracy.fitzgerald.

Pretend that drugs are sub-categories, and doses are sales.

To understand the mechanism, you need to have some knowledge of how table calculation works, for which Tracy kindly provided the link.

If you wanted to show the largest single dose, then simple MAX() does the job.

• ###### 3. Re: return max value for a day, for each month

That is not producing the desired result.

Here's a more detailed explanation...

I fill Viagra.  Some patients get 4, some get 6.  In February, the most that we gave out in one day was 12 - on Feb 6th.  In January it was 16, and we did that on the 9th.

That is important to me, for ordering purposes.  If i know the MOST i'd do in one day, i can set my minimum on hand quantity etc.

So my result set would look like this:

January         February
Viagra     16                    12

Plavix     60                    90

Lipitor     210               140

The original data would look like this:

Feb 6th Viagra Bob Jones #4

Feb 6th Viagra Gary Smith #6

Feb 7th Viagra Sam Sammy #9

Feb 6th Viagra Bill Clinton #6

and so on and so forth for each individual prescription filled.

I'm kind of thinking that this must be some sort of join i'd have to do.  I dont know, you guys are smarter at this...

• ###### 4. Re: return max value for a day, for each month

This post is now in danger of being taken down as spam by auto spam filter because of the frequent use of "Viagra".

Yep, my table calculation method will produce the desired result for your case description. Table calculations could be a bit of 'rocket science' to understand, but, like the proverbial drug in your example, they work as advertised (not that I would know personally).

If you can de-sensitise (now the post will definitely be taken down) your data and post a workbook, I can do the table calculation like in the mock-up example. It didn't work in your case probably because the partitioning was too tricky, which is a hard (this would be the last straw) concept to master because it is not that easy to picture in one's mind.

• ###### 5. Re: return max value for a day, for each month

Thanks a million.  you guys are geniuses and tableau is magical.

Neal