6 Replies Latest reply on Jun 7, 2018 6:03 AM by Bryce Larsen

    Pre Quarter in One Bar with 4 Quarter and month in Bar Chart

    rakesh jadhav

      Hi Team,

      Please find the attachment of .twbx file, I need to display with the following as shown in PNG file

      Means 2018 Q1 to 2018 Q4 as Pre 2018 all data ,then 2019 Q1 2019 Q2 2019 Q3 2019 Q4 , then last graph as Jan 2018 if that Jan 2020 complete then Feb  2020 if March 2020 comes then it will atomically became 2020 Q1.

       

      Please let me know how to achieve this.

       

      Thanks

       

      Rakesh Jadhav

       

        • 2. Re: Pre Quarter in One Bar with 4 Quarter and month in Bar Chart
          Mavis Liu

          Hi Rakesh,

           

          You could create a calculated field such as:

          2018-06-07_13h01_42.png

           

          This means, if the year of the date is not the maximum (ie anything in 2018 and 2019) then it will show in quarters. If the year of date matches the maximum year in the dataset (2020), then it will show the Month and Year:

          Because this date field is now just a text label, you will need to order this so that the time periods are running correctly across the bottom.

           

          I have created a field which is just the yyyymmdd, so that I can use this to sort with:

           

          2018-06-07_13h03_24.png

           

          So if I now bring on the date field to columns, I can right click and sort it ascending based on the sorting field:

           

          2018-06-07_13h04_17.png

           

          So then you get the view above.

           

          However the last thing I could not work out was how to update the group to 2020 Q1 once the Q1 has been completed.

           

          Thanks,

           

          Mavis

          2 of 2 people found this helpful
          • 3. Re: Pre Quarter in One Bar with 4 Quarter and month in Bar Chart
            Bryce Larsen

            I believe you just need to add an additional step in the IF statement to check it Max date is the last month of the quarter.

            If data is monthly (no need to check for 3/31):

            Last Date is end of quarter (CF_Date_Max_EOQ)

            {MAX([Date])} = DATEADD(“month”, -1, DATETRUNC(“quarter“, DATEADD(“month”, 1, {MAX([Date)}

             

            Store CF_Date_Max: {MAX([Date])}

             

            To put it all together:

            IF [Date] < DATETRUNC(“quarter“, [CF_Date_Max]) OR [CF_Date_Max_EOQ]

            THEN STR(YEAR([Date])) + “Q” + DATENAME(“quarter“, [Date])

            ELSE DATENAME(“month“, [Date]) + “ “ + STR(YEAR([Date])

            END

             

            If you want to always group Dates that occurred over a year prior to the max date, you can do this dynamically as well. Add this step at the start:

            IF YEAR([Date]) < YEAR([CF_Max_Date])-1

            THEN “Pre “ + STR(YEAR([CF_Max_Date])-1)

             

            Then continue the IF from before (changing to ELSEIF of course).

             

            Unable to test as I’m on train, but *think* that should work. You can also just SORT on [Date] (Minumum). I do not believe you need to create a separate field to order the new labels.

             

            Bryce

            • 4. Re: Pre Quarter in One Bar with 4 Quarter and month in Bar Chart
              Mavis Liu

              Hi Rakesh,

               

              I think I've worked it out, so you'll need to create another calculation just for quarters:

              2018-06-07_13h24_31.png

               

              Then update the date field calculation to this:

               

              2018-06-07_13h25_08.png

               

              This means that the months will be grouped in quarters if it is no longer the LATEST quarter and year. So this calculation only works when you have a new quarter which 'closes' off the previous quarters.

               

              e.g.

              Months January 2020, February 2020 and March 2020 will ONLY be grouped into 2020 Q1 when April 2020 data is in the dataset.

               

              I've attached the workbook to show you how this works, I've also created an updated dataset which has some new dates in there so you can see whether the calculation works as expected.

               

              To do this, right click on one of the data sources and replace data source:

               

              2018-06-07_13h29_01.png

               

              2018-06-07_13h28_44.png

               

              Thanks,

               

              Mavis

              1 of 1 people found this helpful
              • 5. Re: Pre Quarter in One Bar with 4 Quarter and month in Bar Chart
                Mavis Liu

                Hi Bryce! I was just writing my answer as you posted yours! Yes yours would work, however one of the key issues I had was how to say the quarter had been 'closed', so I chose to say it was 'closed' once the next quarter started. This was because it looks as it the dataset had day, month and year. So I didn't want to go through and check whether it was the last day of the month within that quarter!

                 

                Also you're right, sorting by min date would work as well! I've been so used to creating dates in the format of YYYYMMDD (I don't know why, I just quite like that format..)  that I completely forgot it could be so straight forward...

                 

                Mavis

                • 6. Re: Pre Quarter in One Bar with 4 Quarter and month in Bar Chart
                  Bryce Larsen

                  Mavis Liu That's because that's the only way you should properly store dates! Haha. Really wish that was the standard across the world.

                   

                  rakesh jadhav

                  I've modified my End of Quarter calculation to do see if the max date is the last date of quarter instead. I added a parameter to let you toggle between Max Date and the Date Picker to demonstrate this:

                  Max Date:

                  Date Picker (end of September):

                   

                  Hopefully this helps. Happy I could provide an example!

                  1 of 1 people found this helpful