1 2 Previous Next 20 Replies Latest reply on Sep 22, 2015 6:27 AM by Simon Runc

    Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter

    Abhishek Tyagi

      I've 3 years of data 2012, 2013 & 2014. I've Year, Quarter & Month variable. I've 3 metrics that I need to show on each graph/map through a filter option.

       

      Annualised Monthly Attrition= (Total Attrition/Total Customer) * 12

      Annualised Quarterly Attrition= (Total Attrition/Total Customer) * 4

      Annualised Yearly Attrition= (Total Attrition/Total Customer)

       

      I am looking to add a functionality where user can select Monthly Attrition and any month i.e. May'14 then is able to see 'Annualised Monthly Attrition' metric for May'14.

      Similary in the same filter if the user selects Quaterly Attrition and any quarter i.e. Q1'14 or Q2'14 then he/she is able to see 'Annualised Quarterly Attrition' metric for Q1'14.

      Same functionality applies on Yearly Attrition as well.

        • 1. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
          Simon Runc

          hi Abhishek,

           

          So for the 1st part of your question you can use the parameter as a Date Slice selector.

           

          So create a parameter as a String with the list of 'Year', 'Quarter', 'Month' (and show the control)

           

          Next is to create your calculated field, So would be something like

           

          CASE [Date Slice Parameter]

          WHEN 'Month' THEN  (Total Attrition/Total Customer) * 12

          WHEN 'Quarter'  THEN '(Total Attrition/Total Customer) * 4

          WHEN  'Year' THEN  (Total Attrition/Total Customer)

          END

           

          You can then bring this field onto your Map.

           

          The next would be a filter for the Month/Quarter/Year

          Again you can create a filter based on the parameter

          So would be

          CASE [Date Slice Parameter]

          WHEN 'Month' THEN  [Month]

          WHEN 'Quarter'  THEN [Quarter]

          WHEN  'Year' THEN  [Year]

          END

           

          You can then drag this field onto the filter shelf, and show the quick filter. Then as you change your parameter this filter will change to display the entries if the selected Date Slice than can then be filtered on.

           

          Hope this is what you were after, and is clear how it works. If not, or you have any other questions please post back and I'll go into a bit more detail

          • 2. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
            Abhishek Tyagi

            Hi Simon..Thanks a lot for providing the solution. I need one more help from your side if you could help. This is also related to the definitions I've written in my earlier post.

             

            Instead of 'Year' definition, I need to code the below mentioned YTD definition (same should also reflect in Parameter that I've created. Monthly, Quarterly & YTD)

             

            YTD Definition------((Attrition count year to date until the end of the month chosen / Number of months
            YTD) * 12) divided by the Average (Customers of each month until the month chosen / Number of Months YTD).

             

            The calculation would always start from Jan month. So if a user is selecting Mar'14, the formula would be-

            ((Attrition count from Jan'14 till Mar'14) / 3) * 12) divided by (Average (Customers from Jan'14 till Mar'14) / 3)

             

            Along with Monthly, Quarterly figures, this YTD figure would also go at the back of each chart/Map. So User will have 3 drop down options in the Parameter (calculated field). After that, they can click on any dimension (like country, region etc. and see the % for that specific field values).

             

            I've the month variable in text format (2014/12, 2015/01 etc.) and Quarter variable also in text format (Q1 14, Q2 14 etc.). So rightnow user is able to select 2014/12 if he chooses Monthly option and Q1 14 if he chooses Quarterly option.

             

            I was planning to create a duplicate variable of Month (Month_YTD) that can be linked with YTD figure.

             

            Hope to hear from you soon.

             

            Thanks,

            Abhishek

            • 3. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
              Simon Runc

              hi Abhishek,

               

              So from what you have above it sounds like your Month field (in the data) isn't in 'Dates', but is a string representing the month in the form 2014/01, 2014/02...etc.

               

              So what I'd do here is to create a field which was the integer of the month (this way we can use < operator)

               

              So this field, lets call it 'Month Integer' would be in the form

              INT(RIGHT([Month Field], 2))

               

              I would then create your month selector [a parameter] as an Integer so 1,2,3...12, with the Alias (i.e. what the users sees) as Jan, Feb, March as below

               

              Parameter with Alias.JPG

               

              From here you can create a YtD field based on that selection, so this would be something like (lets call this one 'YtD Attrition')

               

              IIF([Month Integer] <= [Month to YtD Parameter], [Total Attrition],0]

               

              Then do the same for Total Customer (lets call this one 'YtD Customer'

               

              IIF([Month Integer] <= [Month to YtD Parameter], [Total Customer],0]

               

              Now you can use this measure in your selector

               

              CASE [Date Slice Parameter]

              WHEN 'Month' THEN  (Total Attrition/Total Customer) * 12

              WHEN 'Quarter'  THEN '(Total Attrition/Total Customer) * 4

              WHEN  'YtD THEN  (YtD Attrition/YtD Customer)*[Month to YtD Parameter]

              END

               

              as you can see by having your parameter as an integer you can take advantage of <, and also multiply by it in the final formula.

               

              Hopefully this makes sense.

              • 4. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                Abhishek Tyagi

                Thanks Simon for your reply but somehow I'm not able to add the YTD option in the parameter that I created from your first post.

                From your first post, I created a parameter 'Month Type' which had values like Monthly, Quarterly & Yearly(screenshot below) and then a calculated field 'Type of View'.

                If [Month Type]= 'Monthly' THEN  [Year Month Text]            //format and range from '2014/01 to 2015/03'

                elseif [Month Type]= 'Quarterly'  THEN [Quarter Year Text] //format and range from 'Q1 2014' to 'Q1 2015'

                elseif [Month Type]= 'Yearly' THEN  [Year Text]                   //now this is not required

                END

                After creating the above formula, to get Monthly, Quarterly & Yearly customer count, I created another calculated field which is put in the back of each chart

                if [Month Type]= 'Monthly' THEN  [Number of records]

                elseif [Month Type]= 'Quarterly'  THEN [Number of record]/3

                elseif [Month Type]= 'Yearly' THEN  [Number of records]/12

                END

                Now from your second post, I created a calculated field Month Integer= INT(left(right([Year Month Text],3),2)).

                So I can see values from 1 to 12. Then I have created a new parameter which is 'Month to YTD' with Integer option and mapping 1 to 12 as Jan to Dec. Just for testing purpose, I've not yet created a formula for % but I've created a formula which you have given Ytd Attrition= IIF([Month Integer] <= [Month to YTD], ([Attrition Count]),0).

                 

                The problem that I'm now facing is, how do I add YTD option in the dropdown of the parameter (because now I've 2 parameters i.e. 1) Month Type- String option used, 2) Month to Ytd- Integer option used. So facing problem in coding this below formula-

                 

                CASE [Date Slice Parameter]                     // which parameter should I put here?

                WHEN 'Monthly' THEN  [Number of records]

                WHEN 'Quarterly'  THEN [Number of record]/3

                WHEN  'YtD' THEN  [Ytd Attrition]

                END

                 

                Parameter.png

                • 5. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                  Simon Runc

                  hi Abhishek,

                   

                  On my first post I hadn't realised that you actually wanted YtD, in which case I'd have created the parameter as I had in the second post (which also was more of a 'how I'd approach it' rather than an exact solution). It's also tricky to give you an exact solution without a sample workbook to work from.

                   

                  If I can just check my understanding of your final need, and how I currently see it working...

                   

                  So user can choose a 'Time' period, and then (potentially) 'YtD to Period', so the end user has 2 things to select;

                  The 'Time Frame': Month, Quarter, Year, or YtD

                  The 'To Period': This is only required if they select YtD, and is a Month (which when selected, in conjunction with YTD in the first selection), shows the figure annualised to that selected Month

                   

                  Let me know if my understanding is correct? and I'll work you up a workbook from dummy data (better if you can post a 'shareable' version of your one, so I know exactly how your data is structured. This only has to be a few rows, as it's the shape of the data that's important when thinking about the best solution, but if not I can mock something up).

                  • 6. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                    Abhishek Tyagi

                    Hello Simon,

                    Thanks for the reply. I've uploaded a sample of how my data looks like. My original data is on server and it is more than 1M records. So it is very slow. Please provide your directions if you know how to improve the refresh rate of the graphs and maps.

                     

                    In the 'Month Type' parameter, i need to include a YTD option. When I click on that option, i should see the months from Jan'14 to Mar'15.

                    The formula for YTD is (Total Attrition till the month selected / No. of Month YTD) / (Total Customers till the month selected / No. of Month YTD).

                     

                    In the circle chart you can see that if user is selected Monthly and Quarterly option, the figure chages. Similarly I am trying to put YTD as the 3rd option so that user can select that option and select a particular month and see the YTD figures in each graph.

                     

                    I've tableau desktop 8.1 version so request you if you could provide your solution in the same version.

                    Thanks a lot for your support.

                     

                    Regards,

                    Abhishek

                    • 7. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                      Simon Runc

                      ...so how does this look? You may need to do a bit of 'correcting' of the exact formulas you want for the various calculations, but hopefully you can follow the logic.

                       

                      So firstly I created a parameter, similar to yours but with YtD option too. (this is called 'Month Type SR')

                       

                      I then created a calculation so I could turn the Month into an Integer. This ended up being the following, as you have leading/ending ' on some rows and not on others

                       

                      INT(MID([Year Month], FIND([Year Month],'/' )+2,2))

                       

                      I've then also created a field called 'Single Time Select Quick Filter SR'

                       

                      If [Month Type SR]= 'Monthly' THEN  [Year Month]

                      elseif [Month Type SR]= 'Quarterly'  THEN [Quarter]

                      elseif [Month Type SR]= 'Year' then str([Year Text])

                      END

                       

                      This is the Quick Filter for when the user selects Month/Qtr/Year and then brings them back options to filter on just one element (such as selecting a single quarter)

                       

                      I then also created a parameter, as per the previous post, for when a user selects the YtD Option so they can choose which Month the YtD goes to. This is the Integers 1-4 with Month name alias.

                       

                      So now for the logic! I've only created 'Type Attrition' and 'Type of Cust Count' (mine have SR appended)

                       

                      For Type Attrition the formula is

                       

                      if [Month Type SR]= 'Monthly' then (sum([Attrition])/sum([Number of Records]))*12

                      elseif [Month Type SR]= 'Quarterly' then (sum([Attrition])/sum([Number of Records]))*3

                      elseif [Month Type SR] = 'Year' then (sum([Attrition])/sum([Number of Records]))

                      elseif [Month Type SR] = 'YtD' then

                      (sum(IIF([Month Int]<=[YtD Select Month],[Attrition],NULL))/[YtD Select Month])

                          /(sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))/[YtD Select Month])

                      end

                       

                      So when user selects YtD the formula uses the Month In and YtD Month select to only show the YtD figures (and then scales them up by the selected Month (which is an Integer)

                       

                      The formula for 'Type of Cust Count' follows a similar logic

                       

                      if [Month Type SR]= 'Monthly' then SUM([Number of Records])

                      elseif [Month Type SR]= 'Quarterly' then SUM([Number of Records])

                      elseif [Month Type SR] = 'Year' then SUM([Number of Records])

                      elseif [Month Type SR] = 'YtD' then

                          sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))

                      END

                       

                      I've not got 8.1 anymore, but I've hacked the XML code so you should be able to open it (let me know if not)

                      1 of 1 people found this helpful
                      • 8. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                        Abhishek Tyagi

                        Hello Simon,

                         

                        Firstly I would like to say a big THANKYOU for providing me such a great solution to resolve this issue. I've managed to add YTD as a third option in my dropdown and the nos. are coming perfectly fine.

                         

                        The only issue that I'm facing now is whenever I select YTD in the dropdown and Year as 2015 and Month as Sep or Oct or Nov etc. then also it is giving me the figures.

                         

                        Since I only have data from Jan2014 to May 2015, I dont want user to see the incorrect values that are coming after May 2015.

                         

                        Is there a way to resolve this thing in the formulas that you have provided me.

                         

                        Thanks again for your support Simon.

                         

                        Regrads,

                        Abhishek

                        • 9. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                          Simon Runc

                          hi Abhishek,

                           

                          No problem, and glad it made sense.

                           

                          In terms of a 'nice' solution, what we really want is a 'dynamic parameter' where by the parameter would only populate with 'valid' months (a bit like an 'in context' quick filter). This is a bit of a Holy Grail for Tableau...

                          Here is the idea http://community.tableau.com/ideas/1178

                          and Tableau's update on where they are with this Dynamic Parameters: The Results Are In | Tableau Software

                          as you can see with over 1500 votes it is the most requested feature by around 10 times!..but they are on the case!

                           

                          In the meantime, the only thing I can think would be to add an extra IF statement to the formula, such that on the selection of a Month (with the YtD selected) that was greater than the last month of the data would return no data

                          Here's an example, with a extra condition checking that the Month selected <=3 (March), which return null

                           

                          if [Month Type SR]= 'Monthly' then (sum([Attrition])/sum([Number of Records]))*12

                          elseif [Month Type SR]= 'Quarterly' then (sum([Attrition])/sum([Number of Records]))*3

                          elseif [Month Type SR] = 'Year' then (sum([Attrition])/sum([Number of Records]))

                          elseif [Month Type SR] = 'YtD' then

                              If [YtD Select Month] > 3 THEN NULL ELSE

                          (sum(IIF([Month Int]<=[YtD Select Month],[Attrition],NULL))/[YtD Select Month])

                              /(sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))/[YtD Select Month])

                              END

                          end

                           

                          or just put a filter on the view, which would only return data for a valid month. To do this you'd create a calculated field, which is just the entry from the parameter, and when you add it to the filter shelf use custom list to only show 'valid' months (such as the below screen shot). This would have the affect of blanking the screen if they user didn't select a 1,2,or3  (i.e. Jan, Feb, March)


                          Custom Filter.JPG

                           

                          This isn't perfect as firstly you'll need to have the MAX Month number in your data somewhere (btw if you were on Tableau 9.0,you could use a LoD calculation to achieve this), or you could create a parameter, which you'd have to manually update once a month. Also it won't indicate to the user what they have done wrong (I'd hope that being in May, and trying to look YtD to November, would be a clue...but you can never mitigate for an end user!!), but with the screen showing nothing, you would at least not be misleading. You could possibly do a sheet swapping thing so that on that selection it would swap to a sheet with the text ('YtD can only be selected to last month')

                           

                          ...I'll keep the grey cells on the case, and let you know if I think of anything a bit more elegant.

                          • 10. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                            Abhishek Tyagi

                            hi simon,  any particular reason why you have written 3 in the extra if condition.  i've data from jan 2014 to may 2015 so  in my YTD select month filter  i've values from 1 to 12.

                             

                            the problem starts when after selecting YTD option, i select 2015 in year option and then select any month (from YTD SELECT MONTH filter) after may. what  i'm looking for is either i should not see months from jun to dec if i select 2015 or the values for these particular months after selecting 2015 should become equal to zero.

                             

                            thanks,

                            abhishek

                            • 11. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                              Simon Runc

                              hi Abhishek,

                               

                              I only used 3 (March)  as my Month select parameter only goes up to April, so wanted to choose a month before the last month in my parameter to reflect your situation. I always like to test any solution.

                               

                              In my example I've equated the measure, for a month selection after march (Month Int 3), to equate to NULL, but you could easily change this to zero, and once you do the same for the other measures if anyone selects a Month after that the Viz will shows zeros.

                              • 12. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                                Abhishek Tyagi

                                Hello Simon,

                                 

                                Thanks for the reply. Somehow, the below logic is not working. I've changed it to 0. Could you please suggest if there can be an alternate solution to resolve this. Basically, what I want is that for every month (whose data is not available) that I filter (when YTD option is checked), I should see blank.

                                 

                                If [YtD Select Month] > 0 THEN NULL ELSE

                                (sum(IIF([Month Int]<=[YtD Select Month],[Attrition],NULL))/[YtD Select Month])

                                    / (sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))/[YtD Select Month])

                                END

                                 

                                Also, wanted to know one more thing, is it possible to include one more option in the drop down of '12 month rolling average' i.e. I'll have options of monthly, quarterly, YTD and Rolling 12 months avg..

                                 

                                The formula for that would be (Total Terminations in last 12 months) / average(Number of Records of each 12 months)

                                 

                                Thanks,

                                Abhishek

                                • 13. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                                  Simon Runc

                                  ...the spec keeps on growing!!

                                   

                                  I've created a parameter in the attached, which is called 'Last Available Month' (hopefully you could get this 'hard coded' into your data stream, but if not you only need to change this once a month). I've set it up as below

                                   

                                  Max Month.JPG

                                   

                                  I've then got our original equations to reference this (again notice the actual value is an Integer, even though you will see Month Name), in the following way (this is the example for 'Type Attrition SR')

                                   

                                  if [Month Type SR]= 'Monthly' then (sum([Attrition])/sum([Number of Records]))*12

                                  elseif [Month Type SR]= 'Quarterly' then (sum([Attrition])/sum([Number of Records]))*3

                                  elseif [Month Type SR] = 'Year' then (sum([Attrition])/sum([Number of Records]))

                                  elseif [Month Type SR] = 'YtD' then

                                  If [YtD Select Month] > [Last Available Month] THEN NULL ELSE

                                  (sum(IIF([Month Int]<=[YtD Select Month],[Attrition],NULL))/[YtD Select Month])

                                      /(sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))/[YtD Select Month])

                                      END

                                  end

                                   

                                  So if the Selected YtD Month, is > this parameter it returns NULL. I then add a filter on this measure and set to Non-Null Values

                                   

                                  Non Null Values Filter.JPG

                                  This way if say our 'Last Available Month' filter was Feb, and someone tried to select 'March' in the YtD Month selector (appreciate this is getting a little complicated!) it would equate to Null, but as Nulls are filtered the screen would not show anything, as you want.

                                   

                                  Now for the Last 12 Months...now this does get a little complicated!!

                                   

                                  The first thing I need is to create a numbered index from 0 (current Month) back. Again you could hard code this as part of the ETL, but I've created this field from my 'Last Available Month' parameter. I the following way

                                   

                                  DATEPARSE('dd/MM/yyyy', '01/'+str([Last Available Month])+'/2014')

                                   

                                  (as you can see 2015 is hard coded, but you could also parameter this, or better get it in as part of the ETL). As an aside, if you were on Tableau 9.0 you could use LoD calculations to create these fields in Tableau).

                                   

                                  I then create a DateIndex on this field, using Date Difference, in Months between this (the last Month) and each other month, using the below formula

                                   

                                  DATEDIFF('month',DATETRUNC('month',[Month]), [Last Month as Date])

                                   

                                  This has this effect

                                   

                                  Last Date index.JPG

                                  As you can see it counts back to each month. Now we have this, we can add our extra option to the time select parameter, and add an extra condition to our formula creating our values

                                   

                                  if [Month Type SR]= 'Monthly' then (sum([Attrition])/sum([Number of Records]))*12

                                  elseif [Month Type SR]= 'Quarterly' then (sum([Attrition])/sum([Number of Records]))*3

                                  elseif [Month Type SR] = 'Year' then (sum([Attrition])/sum([Number of Records]))

                                  elseif [Month Type SR] = 'YtD' then

                                  If [YtD Select Month] > [Last Available Month] THEN NULL ELSE

                                  (sum(IIF([Month Int]<=[YtD Select Month],[Attrition],NULL))/[YtD Select Month])

                                      /(sum(IIF([Month Int]<=[YtD Select Month],[Number of Records],NULL))/[YtD Select Month])

                                      END

                                  elseif [Month Type SR] = 'Last 12 Months' then (sum(IIF([MonthIndex]<=12,[Attrition],NULL))/12

                                  end

                                   

                                  I've not added the last part to the attach, but have created the Month Index formulas in the attached, but hopefully you follow the logic.

                                  • 14. Re: Funtionality to add monthly, quarterly, yearly in Map/Bar chart through filter
                                    Abhishek Tyagi

                                    Hello Simon Runc,

                                     

                                    I hope you remember me. How are you doing. I need some help from your side again related to the solution that you provided me.

                                    As per your solution, currently I've a parameter (Select View Type) which has 3 options Monthly, Quarterly & YTD.

                                    I've a filter (Select Time Period) which is connected with the above mentioned parameter.

                                    I also have another parameter (Select YTD Month).

                                     

                                    Now as the user makes a selection in first parameter, the filter automatically changes e.g. if user is selecting Monthly view in the parameter, then he can select any of the month from the filter. Similar functionality applies to the Quarter view also.

                                     

                                    For YTD view, the user needs to select year from the filter (2014, 2015) and then again the user needs to select the month from the second parameter. So for YTD view, multiple selections are required.

                                     

                                    Is there a way, if we can eliminate the 2nd parameter (Select YTD Month) and just keep the first parameter (Select View Type) and filter (Select Time Period).

                                     

                                    Monthly & quarterly view would remain the same, but what I want for YTD option is that if a user is selecting 2014 in the filter (after selecting YTD from first parameter), he should see the YTD rate till Dec'14 data. For 2015, it will be calculated till the last completed month (rightnow data till Aug'15 is available).

                                     

                                    Let me know in case any further clarification is required.

                                     

                                    Thanks,

                                    Abhishek

                                    1 2 Previous Next