1 2 Previous Next 20 Replies Latest reply on Jun 30, 2017 7:05 AM by Gerardo Varela

    How to create repeating event based on frequency

    Pawel Mazur

      Hello and greetings to all members of the board as this is my first post. Unluckily, I am quite new to Tableau, and my experience doesn't handle below issue.

       

      What I need to achieve is repeating occurrence of payment as per below graphic without modification to the data source.

      Based on the frequency(once off / monthly / quarterly / annual / seasonal (twice a year)), first payment and last payment date, I want to populate dates for the following payments until last payment date will be reached (everything I have drawn in blue )

       

       

      I have searched board but didn't find anything (besides modification of the data source) unfortunately.

      Attached is .twbx file (version 10.1)

       

      I would be very obliged for any tips here, thanks upfront!

        • 1. Re: How to create repeating event based on frequency
          Simon Runc

          hi Pawel,

           

          I'm sure it's me, but can you explain what the hand-drawn shapes are?

           

          Do they indicate the payment frequency? so blue squares are for monthly, blue crosses are for quarterly...etc.

           

          And the number of them? Looking in the data source I can only see a single entry per Task

           

          Also what do the 4 date fields mean, which one is the payment date?

           

          If you can let me know I can take a look

           

          My initial guess is that you have a "first" payment date, and then depending if you are monthly, quarterly, 6 monthly...etc. you want a mark for every month, quarter, 6-months after that date?...which is possible (I think) but tricky (as you want more marks than there are data points!). If my guess is correct, is there a reason you can't use the transaction level data (which would make this an easy problem to solve)

          • 2. Re: How to create repeating event based on frequency
            Pawel Mazur

            Hello Simon,

             

            Thanks for interest, let me explain:

             

            Hand drawn shapes represent when next payments should occur based on the frequency - in first example it is monthly so if first payment was on 01/01/2015 next should be 01/02/2015, third 01/03/2015 and last as per the column - 30/04/2017

             

            Squares, crosses and circles are for different type of payment - Sierra, Tango and Uniform.

             

            Start Date - is the date when contract start

            Expiry Date - when contract ends

            First payment - when first payment should occur

            Last Payment - when last payment should be done

             

            Your initial guess is right however I don't really need to use shape for 'Type' or even 'Frequency' this can be represented on the row bar so

             

             

            or even

             

             

            Can you please provide some more details, what do you mean by transaction level data ?

             

            Thank you!

            • 3. Re: How to create repeating event based on frequency
              Simon Runc

              hi Pawel,

               

              So that what I thought (feared!) you wanted...

               

              In Tableau we can "densify" (from a rendering perspective) the data...this occurs on (what are known as) Range Aware Pills. These are dimensions, which know their Min, Max and increment...which are basically Dates and Bins)

               

              so in your example I can densify the number of marks drawn to the MIN and MAX of all the start dates (the blue bits are shapes for every day)

               

               

              As you can see this might get us part of the way there, but then we need them to extend out to the last Expire/Payment date (which is a different pill)...

               

              By Transaction Level data all I mean is data that is down to the payment level....so for Bravo you'd have a row/entry for 01/09/2016, 01/10/2016, 01/11/2016....(so we'd have the same number of rows as marks)...but I can see you are going into the future, so I guess this is a planning thing, so the transaction for 2018 doesn't exist yet!

               

              Let me have a think about this over the weekend...

              • 4. Re: How to create repeating event based on frequency
                Simon Runc

                hi Pawel,

                 

                So I've had a think, and have a part solution for you...the reason why it's not a whole solution will become apparent (hopefully!), and could become the whole solution with on extra row in the data (the whole solution, without the extra row, might not be possible, and if it is it will be pretty complicated, so wanted to check in at this point!)...NB be warned this solution (even the part version) is pretty involved!!

                 

                As per my previous post, I said that we can densify the render marks...we can do this by ticking this option (we can do this as DAY of First Payment is Range Aware...Tableau knows it's MIN, MAX and Increment [1 day]).

                btw the Green Shaded bit is just a Reference Bar from Start to Expiry...so no need to create this with Gantt marks

                 

                Missing Values.gif

                 

                So now we have a mark for every Day combination in First Payment date...as you can see (and this is where this is a part solution) it can only create Marks to the MAX date in First Payment. If you could add just one extra row to First Payment Date that was the last date you wanted to look at, say 30/09/2018, then we could densify the marks to that date. You could just do this on a "Dummy" task, and we could hide it in the final version). I'll go through (in a bit) a possible way to do this without this row, but it may not be possible with the data and will get super complicated...well even more complicated than it already is!!)

                 

                So they key to all of this is that we can't access these densified marks directly...they don't really exist is the data. So we have to use a self-referencing PREVIOUS_VALUE to access them in formulas. I've created a bunch of formulas, with the affix "Dense" to signify where I have used this function to access a value to a densified mark.

                 

                So the first one is to get (in an accessible-by-formula way) the First Payment dates...

                [First Payment Axis - Dense]

                IF ISNULL(MIN([First payment])) THEN PREVIOUS_VALUE(MIN([First payment]))+1 ELSE MIN([First payment]) END

                 

                this just says....IF the [First Payment] date is NULL (i.e. it's a densified mark) then use the previous mark and add one. This way we can test against the densified dates in other formulas (which also need to made into a dense version)...btw this is very advanced Tableau (IMHO)

                 

                So next I want to get the actual [First Payment] date for each Task...so I can test this date against the densified First Payment marks.

                [First Payment from Task - Dense]

                IF ISNULL(MIN([First payment])) THEN PREVIOUS_VALUE(MIN([First payment])) ELSE MIN([First payment]) END

                 

                notice we have no +1 here as we want to assess this as the same date for every mark, for each Task.

                 

                Now for the logic...I created this formula

                [Frequency as Month Int]

                CASE [Frequency]

                    WHEN 'Annual' THEN 12

                    WHEN 'Monthly' THEN 1

                    WHEN 'Quarterly' THEN 3

                    WHEN 'Seasonal' THEN 0

                    WHEN 'Once off' THEN 0

                END

                 

                So this changes the Frequency to an Every N Months (eg. Quarterly will be every 3 months)...btw I didn't know the frequency of Seasonal so left this as 0.

                 

                I then need a dense version of this

                [Frequency as Month Int - Dense]

                IF ISNULL(MIN([Frequency as Month Int])) THEN PREVIOUS_VALUE(MIN([Frequency as Month Int]))

                ELSE MIN([Frequency as Month Int]) END

                 

                btw the use of MIN here is just so I have an aggregate, I could use MAX or ATTR...as the VizLoD is Day/Task, the MIN is the same as the actual.

                 

                I'd also add, at this stage...I've created a lot of calculations (in separate fields) to help us step though this, but as all the Table Calcs use the same compute using ([First Payment Date]) we could nest all this into 1 or 2 calcs if you wanted

                 

                So next I want to assess the Difference In Months between the Dense [First Payment] dates (in the Axis) and the actual [First Payment Date] for each Task

                [First Payment to First Payment Axis - Diff in Months]

                DATEDIFF('month',[First Payment from Task - Dense],[First Payment Axis - Dense])

                 

                So if we are looking at a mark for 02/03/2016 and the First Payment (for that task) was 02/01/2016 this would return 3.

                 

                I've also, so we can pick up the same day each month created a calculation that returns the day of the month for each Task, when the payment is due (btw you'd need to adjust this day if the first payment day is >=29...as the day 29+ doesn't exist in every month...but we'll assume that it is!)

                [First Payment - Day - Dense]

                DATEPART('day',[First Payment from Task - Dense])

                 

                and we can check if the Densified [First Payment] is the same day as the actual [First Payment] (per Task)...we only want to mark the same Day of Month

                [First Payment Day = Same Day in Month]

                [First Payment - Day - Dense] = DATEPART('day',[First Payment Axis - Dense])

                 

                If we do everything at the Month (TRUNC) level, we don't need to mess about with days...but in your example you were down at day level, so I've kept with this.

                 

                Now for the actual logic...with our [Frequency as Month Int] we only want a mark if the Difference in Months between the Densified [First Payment] and the actual [First Payment] (per Task) is an exact multiple (btw % is the Modulus operator, which return the remainder from a division)...

                [Marks]

                IF [First Payment Day = Same Day in Month] AND

                [First Payment to First Payment Axis - Diff in Months]%[Frequency as Month Int - Dense]=0 AND

                [First Payment Axis - Dense] <= [Last Payment from Task - Dense] THEN 1

                ELSE 0

                END

                 

                so what this says is...If The Day of the Month (for the mark/axis value) is the same as the [First Payment] , day of Month, AND the difference between the [First Payment] axis value and the [First Payment] (per Task) is an exact multiple of the Frequency, AND the [First Payment] axis value isn't >= the Last Payment date (again I have a densified version of this) then mark it a 1, else 0.

                 

                I've kept the logic simple here...but we can easily add more than just the 1 and 0 if we want to mark/colour different events...eg we could add if this is the actual First Payment then = 2...and then we can have a different shape/colour for the first payment. And the same for the last one. I also haven't tested for "One-Off"...but would be easy to add it.

                 

                Once we have this we get a different return value depending if we want a mark or not. I bring this onto the Shape tile...and set the 1 to a square, and the 0 to a blank shape, so it doesn't show (I've attached a transparent shape here...so you can put it your shape repository to use...I find it a very useful shape to have!)

                 

                and voila

                 

                 

                I've also added the important information to the tooltip, so you can hover over the marks as see what's going on.

                 

                So as you can see I can only create marks to the last date (for any task) in First Payment, which is 01/05/2017. If you create the dummy row of 30/09/2018...this would go out that far.

                 

                If you are unable to add this row, one solution might be to use one of the other date fields for the rest of the dates, and dual axis them.

                 

                 

                 

                I'd advise against this as...

                1. it's going to get very complicated as we'd need to assess which Tasks could be completed in the [First Date] range, and when we'd need to start new marks.

                2. We can only use one of the other 3 fields as we can only dual 2 axis,

                3. it's unlikely (for any data) that [First Payment] and some other date field will form a full subset of all dates for all tasks

                 

                I'd also point you to one other thing I found...we have enough dates to get the 2nd payment for Delta (the below image shows the tooltip for the mark 16/11/2017), where we'd expect a payment on 06/11/2017 (one year on from the first), but as the [Last Payment Date] is 29/10/2017, which is before 06/11/2017 our rule set won't put a square on 06/11/2017)...so maybe a bit of DQ to do here.

                 

                 

                OK I'll leave you to digest all that...and I hope that is helpful and makes a bit of sense

                 

                ...right I'm off for a lie down

                1 of 1 people found this helpful
                • 5. Re: How to create repeating event based on frequency
                  Pawel Mazur

                  Hello Simon,

                   

                  Firstly appreciate for your involvement - I am off whole week until next monday - as soon as I will get Through your idea, I will let you know!

                   

                  Many, mamy thanks and hear you soon!

                  • 6. Re: How to create repeating event based on frequency
                    Gerardo Varela

                    Just a tad bit closer. I used a technique to extended the max value of First payment date so you can densifiy marks to 30/09/2018, but I still don't know what to do about Delta.

                     

                    Regards,

                    Gerardo

                    2 of 2 people found this helpful
                    • 7. Re: How to create repeating event based on frequency
                      Simon Runc

                      hi Gerardo,

                       

                      Awesome work ...I hadn't thought of doing that (definitely going into my, ever growing, folder of Tableau tricks!!)

                       

                      and with Delta, I guess it's either a DQ thing, or the Last Payment date always generates a (visible) mark, even if it's outside the exact payment date we'd expect...now we have your trick this would be really easy to add.

                       

                      Although personally I'd densify the data at the DB end (if I was making a production version of this)...this has been a really fun puzzle!!

                      • 8. Re: How to create repeating event based on frequency
                        Gerardo Varela

                        Thanks Simon!  I have to say you did all prep work, the cooking, and the plating.  I just came by and garnished the plate.  By the way, I first saw the trick used by Ɓukasz Majewski in this thread:

                         

                        Got a fun Educational Brain Teaser

                         

                        Regards,

                        Gerardo

                        1 of 1 people found this helpful
                        • 9. Re: How to create repeating event based on frequency
                          Pawel Mazur

                          Hello Guys,

                           

                          Thank you very much for your deep step by step walkthrough Simon Runc and thanks to Gerardo Varela for final touch on this.

                           

                          If the wheel wouldn't be invented yet, I would probably firstly invent it and then (after few years) I would find previous_value logic you implemented.

                           

                          Simon Runc - I'd also point you to one other thing I found...we have enough dates to get the 2nd payment for Delta (the below image shows the tooltip for the mark 16/11/2017), where we'd expect a payment on 06/11/2017 (one year on from the first), but as the [Last Payment Date] is 29/10/2017, which is before 06/11/2017 our rule set won't put a square on 06/11/2017)...so maybe a bit of DQ to do here.

                           

                           

                          Gerardo Varela - I still don't know what to do about Delta.

                          Forget about Delta, I think there should not be case like this, wrong assumption on my side, apologies

                           

                           

                          So as you can see I can only create marks to the last date (for any task) in First Payment, which is 01/05/2017. If you create the dummy row of 30/09/2018...this would go out that far.

                           

                          If you are unable to add this row, one solution might be to use one of the other date fields for the rest of the dates, and dual axis them.

                           

                          I will stick to Gerardo solution as long as below can be solved.. of course with your help

                           

                           

                          When published sheet 11 to Tableau Server it looks like marks are gone as per below screenshot:

                           

                          Again, please help

                          • 10. Re: How to create repeating event based on frequency
                            Simon Runc

                            hi Pawel,

                             

                            Excellent stuff...I myself have re-invented the wheel many many times...of course thinking I was inventing, not reinventing!!!

                             

                            On the Tableau server problem...did you just load the model up, or did you make the data sources "server side" (i.e. switching out the local ones for server side...which can now be done with a tick-box in 10.0+). The switching of data-sources (even when Tableau does it!) is an imperfect switch...and often I find a few things (especially in Table Calcs) get broken (or changed). Once you've done the switch, go back into the dashboard (now being run off server side data sources) and you may need to re-set up the Compute Using/Addressing on the Table Calcs, and also check the "Show Missing Values" is still ticked. You can then republish.

                             

                            hope that does the trick, if not let me know and I'll try it out on mine (I've not used data densification on server/live models so not sure if that is something that gets broken/changed)

                            • 11. Re: How to create repeating event based on frequency
                              Pawel Mazur

                              I have just tried both ways, having source locally stored, and on Tableau Server side (+re-setting Computing and Show Missing Values options) with no success

                               

                              Strange thing is that the 'preview' of the sheet is displaying marks as below

                               

                               

                              when clicked its gone

                              • 12. Re: How to create repeating event based on frequency
                                Simon Runc

                                Very odd...yes the preview is a .png tableau takes/stores when it loads up the model (or refreshes the datasource). So it's getting the "correct" implementation from somewhere! Which server version are you using?

                                 

                                I'm out and about today, but I'll load it up to our Dev server and see what happens!

                                • 13. Re: How to create repeating event based on frequency
                                  Pawel Mazur

                                  Tableau Server Version: 10.1.4 (10100.17.0118.2108) 64-bit

                                   

                                  waiting for some good news then!

                                   

                                  thanks!

                                  • 14. Re: How to create repeating event based on frequency
                                    Gerardo Varela

                                    Hi Pawel,

                                                    I published the workbook to my server (10.2.1) and it worked as expected.  I’m a bit confused as to why it isn’t working for you. Try publishing the workbook I’ve attached in my earlier post as is. See if that works. Make sure you include external files pictured below.

                                     

                                    If you changed the data source to something else, try creating an extract before publishing the workbook.  It may be that your data source doesn’t support a function the workbook is using.  If that doesn’t work, I’m not sure what else to try.

                                     

                                    Regards,
                                    Gerardo

                                    1 2 Previous Next