6 Replies Latest reply on Apr 4, 2013 1:08 PM by Jim Wahl

    How to count days of week in tableau?

    Kiran v

      This might be a fairly simple question but a tricky one. How to count days of week or does a formula exist where days of week could be counted.


      Example- In the month of May there are 4 sundays if those 4 sundays could be multiplied to the number lets say 400 gives me 1600 and I could put that in the view somehow.



        • 1. Re: How to count days of week in tableau?
          Jim Wahl

          Check out this thread:

          The specified item was not found.


          If you want to exclude holidays, then check out this thread about adding using a separate date table:

          Would it be possible to get count of weekdays between two dates



          1 of 1 people found this helpful
          • 2. Re: How to count days of week in tableau?
            Kiran v

            Thank you Jim, but I have another problem facing now seems like the dataset i have the window avg formula works for me if I have to circumvent my data restrictions and need the right view.


            Gives me the error '' you cannot aggregate with non-aggregate functions ''. All i need to do is take an average of all the percentages in the row level and for that window avg formula is perfect but does't work.

            • 3. Re: How to count days of week in tableau?
              Jim Wahl

              Hi Kiran,


              It's a little hard to say for sure without an example, but window_xxx functions need to operate on aggregates.


              For example, WINDOW_AVG(AVG(Sales)) works, but WINDOW_AVG([Sales]) typically won't, since [Sales] has not been aggregated. Depending on the view layout, however, sometimes AVG([Sales]) == [Sales], because there is only one data point / row or at the level of detail. In this case, AVG(), MIN(), MAX() will all return the same value, as will ATTR().


              I'm not sure if that's clear, but if you post a sample of your workbook, I'm sure someone can help you and it will be easier to understand.



              • 4. Re: How to count days of week in tableau?
                Kiran v

                Hey Jim,


                I tried to create a sample set and build a worksheet. Now what happens when i create a calculated field, all i want is to keep the calendar view in sheet 2 intact as it gives me a daily percentage but when i go on sheet 1 and want to see the same data broken down by rate name and want to average it up unless i average it at the view level under marks (which i dont want to do ) i cannot average the percentage at the DOW level of detail.


                for example in sheet 1 i am looking at '' sundays ''  and it gives me 27 percent well i dont want to see 27 percent i want to see 27 % divided by all sundays in the month of april which will give me 6.7 %.


                I don't know but i exhausted all the options but couldn't figure out. Please help!


                thank you in advance

                • 5. Re: How to count days of week in tableau?
                  Kiran v

                  Forgot to mention 27 % in sheet 1 for rate name Onyx.com on Sunday.

                  • 6. Re: How to count days of week in tableau?
                    Jim Wahl

                    Thanks for the sample. I'm about to timeout here, but perhaps there's an easy solution.


                    First, are you sure your Sheet 2 is correct? Do you want to sum the "percent share" for each row? Or do you want to find the percent share for the sum of all units?


                    If I right-click on Wednesday, Week 14 > View Data > Underlying Data, I see that there are four rows of data. For each row you're calculating a "percent share" ---[Occupied Units]/[Total Number of Units]---and then you're summing these to get 9% (2% + 2% + 3% + 2%).


                    I'm not sure this makes sense. If you had four rows with 33% percent share each, you'd get 133%?


                    If on the other hand you want to find the percent share for all units---33% in my example---you'd want to aggregate before dividing:

                    [percent share 2] = SUM([Occupied Units]) / SUM([Total Number of Units])


                    In your case, the 9% goes to ~2%. This should also fix your problem in Sheet 1.


                    Again, I'm not positive this is the issue, mostly because I'm not familiar with your data or the view you're creating.


                    But it's helpful to do a few of the calculations manually in Excel---you can copy the data in View Data pane easily to Excel---to make sure they're working correctly.


                    If this is not the issue, then perhaps someone else can chime in. I'll be off line until tomorrow morning.