11 Replies Latest reply on May 17, 2018 8:34 AM by Bryce Larsen

    Date parameter and/or date calculations??

    Mark Alvarado

      What I am trying to accomplish is comparing a count of a dimension for the most recent 3 weeks against the the previous 3 weeks.  I have a parameter so the user can filter the data to the last 3, 6, 9, or 12 months. But I also want to be able to show when the parameter is filtered to the most recent x number weeks it will compare it to the previous x number of weeks. On the viz below the circles rep the most recent x number of weeks and i want the bar to show the previous x number of weeks.  any thoughts on how to accomplish this?

       

       

       

        • 1. Re: Date parameter and/or date calculations??
          Shinichiro Murakami

          HI Mark,

           

          Please attach your sample data as twbx format.

           

          Thanks,

          Shin

          • 2. Re: Date parameter and/or date calculations??
            Bryce Larsen

            Hi Mark Alvarado,

            Do you mean you want the line to be previous week? Meaning the line could pass the circle? Or are you planning to do a lollipop graph with the line either going up or down based on performance from the previous n weeks?

             

            Regardless, I think you could double the number of weeks the user selects for your date filter and then flag each set using a simple IF-ELSEIF Statement.

             

            Here are a couple calculations I made:

            CF_PeriodStart_Current:

            DATE(DATEADD("week", -[PA_NWeeks]+1, DATETRUNC("week", [CF_ReportWeekEnd])))

             

            CF_PeriodStart_Previous:

            DATE(DATEADD("week", -[PA_NWeeks]*2+1, DATETRUNC("week", [CF_ReportWeekEnd])))

             

            CF_Period_Flag:

            IF [Date] >= [CF_PeriodStart_Previous]

                AND [Date] < [CF_PeriodStart_Current] THEN "Previous"

            ELSEIF [Date] >= [CF_PeriodStart_Current]

                AND [Date] <= [CF_ReportWeekEnd] THEN "Current"

            END

             

            This might get you started. If you're doing a lollipop chart with difference from previous then you can group the dates using similar fashion (essentially changing the "flag" to a date rather than a string).

             

            Best,

            Bryce

            • 3. Re: Date parameter and/or date calculations??
              Mark Alvarado

              Thanks Bryce - here is where i am at now.  So the dot represents the current value and the line is the previous value.  Now i want to bar to start where it ends and run up to the current value or the dot. 

              i guess it is the difference between the the current value and previous value.

               

               

               

              • 4. Re: Date parameter and/or date calculations??
                Mark Alvarado

                Like the view below but i would like a line from one dot to the other dot.

                 

                 

                • 5. Re: Date parameter and/or date calculations??
                  Bryce Larsen

                  Gotcha. I suppose I should have called it a dumbbell chart instead! How's this?

                  You should have CNT(OSD Reason) on your rows measures twice.

                  Make one a circle:

                  • Size: period flag
                  • Color: OSD

                   

                  Make the other a line:

                  • Path: period flag

                   

                  Hopefully this helps!

                  • 6. Re: Date parameter and/or date calculations??
                    Mark Alvarado

                    so the version of the workbook you sent me is too new for my current version and it will not open.  When you state "period flag" what exactly do you mean?

                    On my row shelf i have two different count of PO numbers differentiated by current and previous.

                     

                     

                    • 7. Re: Date parameter and/or date calculations??
                      Bryce Larsen

                      Yep - you'll have to get rid of those unfortunately! But imagine you can take some of the logic to make what I called CF_Period_Flag in a previous post. Essentially you just need to flag dates as either "Current" or "Previous" so you can use these in the pane.

                       

                      Here's my two cards:

                      Circle -

                      Line -

                       

                      You can imagine it's a new measure with the code CNT([OSD Reason]) instead of SUM([Incidents]) for the measures.

                      • 8. Re: Date parameter and/or date calculations??
                        Mark Alvarado

                        Well here is my logic for current count (it uses a parameter):

                         

                         

                        and here is my logic for previous counts:

                         

                         

                        I am not understanding how to I guess "combine" these two calculations into one??

                        • 9. Re: Date parameter and/or date calculations??
                          Bryce Larsen

                          Hi Mark Alvarado,

                          So, my suggestion would be to follow the steps from above: Re: Date parameter and/or date calculations??

                           

                          Instead of going straight to assigning [OSD Reason] based on date range, I'd suggest storing the two date ranges first. I see these say using month when OP said week - I'll use month to tie with what you put here.

                           

                          Can you convert the Parameter to INTEGER instead of having to do a nested IF? Then you can simply do:

                          CF_PeriodStart_Current: DATEADD("month", -[Select View], TODAY())

                           

                          and similar for CF_PeriodStart_Previous:

                          NOTE: as an aside, if you're using TODAY() as you Report Date for the dashboard, consider truncating that to similar date type ("month", in this case). Otherwise you'll have some months that are both "Current" and "Previous".

                          If you truncate it, then you should add 1 to your DATEADD so you're not going back too far, assuming you want the current month as part of the current period:

                           

                          The Flag is then just using these two fields in a Date Comparison with your relevant Date:

                           

                           

                          Lastly, your filter for the sheet can be this field:

                           

                          Now you shouldn't have to null out the OSD Reason. Simply do a COUNT([OSD Reason]) with the views shared previously as the filter and items on the marks cards should take care of things appropriately.

                           

                          Fingers crossed!

                          1 of 1 people found this helpful
                          • 10. Re: Date parameter and/or date calculations??
                            Mark Alvarado

                            Thank You Bryce!! your advice worked.  Below is the finished product.  The only thing i wish i could play with is the positioning of the labels on the dumbbell chart..

                             

                            • 11. Re: Date parameter and/or date calculations??
                              Bryce Larsen

                              Perfect! One suggestion is to switch the two marks in the measures pane - this would put the line behind the two circle points, which might look nicer and make labels easier to read.

                               

                              Also, you could test making a new calculated field for the labels using CHAR(10), but maybe a little excessive. Heh.

                               

                               

                              In truth, you might actually need to do a comparison calc instead and add the CHAR(10)s to whichever circle is LESS as to not have both labels in between the circles. Heh. I tried doing so with the following:

                               

                              Best of luck!