6 Replies Latest reply on May 14, 2018 7:56 AM by Okechukwu Ossai

    How to get the number of dates at different aggregation level

    Bach Nguyen

      Hi,

       

      I have the table below where column A is Calendar Date, Column B is Calendar Month and Column C is Fiscal Month. My company is using different Fiscal calendar other than Normal Calendar

       

      Is there any way that I summarize the data in Tableau as Fiscal Month, but I can get the Count of Days as Calendar Month days as below table? For example as below summary table, I would want tableau to give me that result

       

       

      Thank you.

        • 1. Re: How to get the number of dates at different aggregation level
          kumar bharat

          Hi Nguyen,

          Please check the below it will help you out

          Tableau Public

          Tableau Public

          Tableau Public

          Tableau Public

           

          you can download the workbooks and view the underlying data and logic.

          BR,

          bharat

          • 2. Re: How to get the number of dates at different aggregation level
            Okechukwu Ossai

            Hi Bach,

             

            This one is a bit tricky. The solution below definitely works for the example dataset you provided. However, you have to test it with your actual database to see if is what you need.

             

            Step 1: Create calculated field [Number of Calendar Days]

            MIN(IF [Fiscal Month] = [Calendar Month] THEN {FIXED [Calendar Month]: COUNTD([Calendar Date])} ELSE 0 END)

            Step 2: Create calculated field [Number of Fiscal Days]

            {FIXED [Fiscal Month]: COUNTD([Calendar Date])}

            Hope this helps.

            Ossai

            • 3. Re: How to get the number of dates at different aggregation level
              Bach Nguyen

              Hi Ossai,

               

              This worked for me but I had to change MIN to MAX. I just wonder why I cannot just use the formula {FIXED [Calendar month]:COUNT([Calendar day])} alone? If I translate that formula, it would be for each Fiscal month, calculate the count of number of days for that Month in column Calendar Month.

               

              Thank you.

               

              Bach

              • 4. Re: How to get the number of dates at different aggregation level
                Okechukwu Ossai

                Hi Bach,

                 

                {FIXED [Calendar month]:COUNT([Calendar day])} alone will not work since it doesn't contain any reference to fiscal month. This is because you want to display your result for each fiscal month.

                 

                What the main formula does is to count the number of calendar days for each calendar month. Then it checks if fiscal month is equal to calendar month. If true it display the count of calendar days. If no, it displays zero. Wrapping the calculation in MIN() or MAX() helps to prevent the number of calendar days being duplicated by the number of records.

                 

                Hope this helps.

                Ossai

                1 of 1 people found this helpful
                • 5. Re: How to get the number of dates at different aggregation level
                  Bach Nguyen

                  Hi Ossai,

                   

                  Thank you very much for the quick response. Just 1 more thing on your first explanation. I thought that when I drop Fiscal Month into the row field, it is already the reference for the formula {FIXED [Calendar month]:COUNT([Calendar day])}  ? What I thought when only using that formula is that with the Fiscal Month as a Reference, such as "Jan" in the tableau view, the formula will calculate the number of days for Jan from the column "Calendar Month" because I'm fixing that column, returning 12.

                   

                  It would be much appreciated if you can let me know why I'm understanding wrongly here.

                   

                  Thank you.

                   

                  Bach

                  • 6. Re: How to get the number of dates at different aggregation level
                    Okechukwu Ossai

                    Hi Bach,

                     

                    Recall that in my first response, I told you this problem is a tricky one. This is exactly the reason why I made that statement.

                     

                    Dropping Fiscal Month in the view will not help in this case. Why? This is because Fiscal Month doesn't not always have a like-for-like relationship with Calendar Month. For example, look at the image below

                    Fiscal Month doesn't have a row level match with Calendar Month . Dropping Fiscal Month to the view and using {FIXED [Calendar month]:COUNT([Calendar day])} will assign the count of 12 to both Dec and Jan Fiscal Months. You could try this and see for yourself. This is why there is an IF statement saying return this count of 12 only if Fiscal Month matches Calendar Month otherwise return zero.

                     

                    Hope this helps.

                    Ossai

                    1 of 1 people found this helpful