5 Replies Latest reply on Sep 7, 2018 9:46 PM by Ankit Bansal

# How to Calculate Total per Day?

Hi!

I'm a total novice and I'm looking for some help with my data. I have a list of homes for sale that is pulled twice or three times a day. I would like to know how many homes are listed per day. I can't seem to just sum the number of listings because the total is double or triple what the actual number should be (since this info is pulled twice or three times a day). Is it possible to find the maximum quantity of homes listed in each day? If the number of listings was pulled three times a day and totaled 25, 20, 15 - I'd like Tableau to graph only the maximum (25).

You'll see that in the attached graph, every day has double the actual listings and Sept 12 actually has triple the listings since the listings were pulled three times that day.

Some dates (date compiled) might have two or more lines of data. For example, Sept 7 20:43 has two lines that must be added up to determine the total number of listings. The total number of listings for Sep 20:43 is 24 (10+14). However, the graph shows Sep 7 for Atlanta show as 47 listings which is the sum of all listings from that date (10+14+23) but it should instead show the maximum listings of 24 which is for sep 7 20:43 (10+14) which is higher than the other line from Sep 7 8:43am (23).

• ###### 1. Re: How to Calculate Total per Day?

Hi Kevin,

I played with your workbook and came up with the formula which gives the max number of houses for sale.

Just drag this on to your chart.

{ FIXED [City],DAY([Date Compiled]):MAX([Number of Homes For Sale])}

Hope this helps.

• ###### 2. Re: How to Calculate Total per Day?

Thanks, I'm not sure that dragging it is working. Can you please explain to me how to implement this formula into the workbook?

• ###### 3. Re: How to Calculate Total per Day?

Attaching Screenshot

• ###### 4. Re: How to Calculate Total per Day?

Thank you! I had to make one change to the data - the workbook is updated above. Some dates (date compiled) might have two or more lines of data. For example, Sept 7 20:43 has two lines that must be added up to determine the total number of listings. The total number of listings for Sep 20:43 is 24 (10+14). However, the graph shows Sep 7 for Atlanta show as 47 listings which is the sum of all listings from that date (10+14+23) but it should instead show the maximum listings of 24 which is for sep 7 20:43 (10+14) which is higher than the other one line from Sep 7 8:43am (23).

• ###### 5. Re: How to Calculate Total per Day?

Kevin,

This should work for you:

{fixed [City],day([Date Compiled]) : max(

{FIXED [City],[Date Compiled]:sum([Number of Homes For Sale])}

)

}

You need to use nested LOD. First u need to aggregate data at date compiled and then find max for each day.