
1. Re: How to create repeating event based on frequency
Simon Runc Apr 27, 2017 10:44 AM (in response to Pawel Mazur)hi Pawel,
I'm sure it's me, but can you explain what the handdrawn 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, 6months 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 Apr 28, 2017 1:34 AM (in response to Simon Runc)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 Apr 28, 2017 8:23 AM (in response to Pawel Mazur)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 Apr 30, 2017 6:36 AM (in response to 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
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 selfreferencing 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 accessiblebyformula 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 "OneOff"...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

Empty Shape_10.png 146 bytes

5. Re: How to create repeating event based on frequency
Pawel Mazur May 1, 2017 12:10 PM (in response to Simon Runc)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 May 2, 2017 11:39 AM (in response to Simon Runc)2 of 2 people found this helpfulJust 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

7. Re: How to create repeating event based on frequency
Simon Runc May 3, 2017 2:12 AM (in response to Gerardo Varela)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 May 3, 2017 6:35 AM (in response to Simon Runc)1 of 1 people found this helpfulThanks 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

9. Re: How to create repeating event based on frequency
Pawel Mazur May 9, 2017 7:06 AM (in response to Simon Runc)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 May 9, 2017 7:24 AM (in response to Pawel Mazur)hi Pawel,
Excellent stuff...I myself have reinvented 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 tickbox in 10.0+). The switching of datasources (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 reset 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 May 9, 2017 7:42 AM (in response to Simon Runc) 
12. Re: How to create repeating event based on frequency
Simon Runc May 9, 2017 7:45 AM (in response to Pawel Mazur)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 May 9, 2017 8:05 AM (in response to Simon Runc)Tableau Server Version: 10.1.4 (10100.17.0118.2108) 64bit
waiting for some good news then!
thanks!

14. Re: How to create repeating event based on frequency
Gerardo Varela May 9, 2017 8:44 AM (in response to Pawel Mazur)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