1 2 Previous Next 21 Replies Latest reply on Jul 23, 2018 8:59 AM by Scott Schmeling

    Window function without any Sum or Average

    Scott Schmeling

      Good Morning,

       

      I am trying to create a table that has the following columns:

       

      Previous, Current, Difference, 3 month sparkline.

       

      I am looking to see if there is a Window function or someway to turn a calculation into a header row.  I can't use Window Average as the data needs to be able to be filtered by a location as well as all.  You can see by the image the current way it is working- I'm just not able to filter by location and get it to work as I need the flexibility while figuring %'s.  I know this is not the best explanation- I apologize.  Hopefully the picture will give you a better idea.  Thanks.

       

        • 1. Re: Window function without any Sum or Average
          Joe Oppelt

          I think you are asking two different questions.

           

          Are you looking for a way to put a header above the column of graphs?  If so, put it on a dashboard and float a text box there.

          • 2. Re: Window function without any Sum or Average
            Joe Oppelt

            I don't understand what you are looking for with respect to filtering.  Why can't you just add a filter on the sheet?  Do you want a window_function to operate on the whole data set independent of the filter?

            • 3. Re: Window function without any Sum or Average
              Scott Schmeling

              Hi Joe,  In order to get the two different time periods

              Trailing 3 months for the Spark line and only 2 months for the data portion.  I had to use Table calcs.  To get the Current and the Previous columns and then to calculate the growth.  This was the only way I was able to put them all on the same row with the sparkline.

               

              In order to get the formulas to work correctly I had to use Window functions specifically Window_AVG.  I'm trying to find another way to get the same results without having to use the Window_AVG as it causes issues when I try to filter by location.  Here's teh formula I ended up using.

               

              Window_AVG(AvG(iif([Last Month],([Enrolled Fixed Top]/[Internal Fixed Top]),null)))

               

              Where

              Last Month is boolean

              and the others are fixed functions.

               

              I know it might be too confusing to explain without a WB but I don't have one to post right this minute and it will take me several hours to clean it up from Hippa information to post it.

               

              I'm throwing a hail mary out there.

              • 4. Re: Window function without any Sum or Average
                Robertino Bonora

                Hello, can you share the .twbx to see how you are calculating the measures.

                 

                By the way, if you turn Trending to discrete doesn't show the header?

                 

                Another solutions maybe could be add in the column shelter the dimension "Measure Names", next add the same dimension "Measure Names" to the filters and select wherever measure you want to display, and last, add the measure "Measure Values" to the Text marks and you will see something like this,

                 

                I dont know if this is what you were expecting, if not, please explein yourself a little more

                 

                 

                PD: Months are in spanish

                • 5. Re: Window function without any Sum or Average
                  Scott Schmeling

                  This is not going to work as I described it- so trying a new tact.

                  • 6. Re: Window function without any Sum or Average
                    Joe Oppelt

                    If you use a table calc as a filter to select your locations, the whole underlying table remains intact and your other table calcs will work correctly.

                     

                    Try this:

                     

                    LOOKUP( ATTR([Location]),0)

                     

                    Use that calc as a filter.  It will look and feel just like a standard filter, but it will allow your table calcs to work on the sheet.

                     

                    If this doesn't get you there, I'll wait for your sample workbook.

                     

                    If you  need help anonymizing your data, check out this 10 minute video:

                     

                    Video demonstrates how to anonymize your workbook/data

                    • 7. Re: Window function without any Sum or Average
                      Scott Schmeling

                      Thanks Joe,

                       

                      I'll get the workbook uploaded this isn't going to do what I need.  I think it will make much more sense when I get a WB up- it's just a pain anonymizing it as it is quite a few rows and there are several rows per Client ID.  That's the entire issue of anonymizing. 

                       

                      We are missing something in the communication.  I do appreciate your time on this, I'm just frustrated as  I need to get rid of the Table Calcs- but it is just not working the way I want.  I could do this in a heartbeat in SQL- just not in Tableau.

                      • 8. Re: Window function without any Sum or Average
                        Scott Schmeling

                        Hi Joe,

                         

                        Here is the Workbook.  I'm basically trying to be able to filter this table by location as well.  Right now nothing will change when you change Location as it is set at the Top LOD meaning for all data.  But I want to be able to set it at the Location LOD while still running the rest of the Workbook as is.

                         

                        Thanks,

                        • 9. Re: Window function without any Sum or Average
                          Joe Oppelt

                          (V 10.3 here)

                           

                           

                          Scott Schmeling wrote:

                           

                           

                          ...But I want to be able to set it at the Location LOD while still running the rest of the Workbook as is.

                           

                          Thanks,

                          I first want to understand this statement.

                           

                          There are a chunk of measures under a section, "Location LOD Calcs", and a chunk under "Top LOD Calcs".  The sheet in the workbook is using Top LOD calcs.

                           

                          Are you saying that for this sheet, you want to display Top LOD calcs, but be able to filter for Location, yet still use those Top LOD calcs elsewhere while they are looking at the entire data set?

                          • 10. Re: Window function without any Sum or Average
                            Scott Schmeling

                            No- When I switch to Location LOD calcs, you will notice the formula does not work correctly.   When I switch to the MCA LOD calcs, it seems to filter out those locations that have Internal Referrals but no Enrollments so my % actually creeps up a little.  What is showing as 44% becomes 48%.  As well, I'm not sure any other way to have the current and previous on the same row as the Sparkline without using the table calc setup.

                            If I don't use a table-calc I end up with a row for previous, a row for current and a blank row for the third month of the sparkline. 

                             

                            Honestly I don't care how it's done, I just need to find the easiest way that will give me the correct answer while maintaining the look as it is. 

                             

                            I really appreciate any suggestions you have on this.  Lastly- could you point me to some place where I can learn these deeper more advanced calculations.  I'm really looking to increase my knowledge and not just get answers from people.

                             

                            Thank you so much Joe.

                            • 11. Re: Window function without any Sum or Average
                              Scott Schmeling

                              What's really causing the issues I think Joe is that we are trying to maintain this look of 2 different time frames. 

                              Time frame 1 for the crosstab - of last 2 months with a difference column.  Time frame 2 for the spark lines which is trailing 3 months.

                              • 12. Re: Window function without any Sum or Average
                                Vishnu Sekhar

                                Hi Scott,

                                 

                                I am not sure whether this will be of any help. Always happy to lend a hand

                                 

                                Screen Shot 2018-07-20 at 2.47.41 PM.png

                                 

                                This is not a direct 'one sheet' solution. Please review and let me know how it goes.

                                 

                                Regards

                                Vishnu

                                 

                                • 13. Re: Window function without any Sum or Average
                                  Scott Schmeling

                                  Thanks Vishnu- I have till Monday to get this figured out- so I'm going to keep trying the one sheet solution before I create two sheets and line them up. 

                                   

                                  It's really pushing my learning as well, so not against it.  I've been trying to follow this example and modify it, but my date calc is not working on my data. 

                                   

                                  Tableau Tip: KPIs and Sparklines in the Same Worksheet

                                   

                                  Just need to keep plugging away but always appreciate any and all help.

                                  • 14. Re: Window function without any Sum or Average
                                    Joe Oppelt

                                    Scott Schmeling wrote:

                                     

                                    No- When I switch to Location LOD calcs, you will notice the formula does not work correctly. When I switch to the MCA LOD calcs, it seems to filter out those locations that have Internal Referrals but no Enrollments so my % actually creeps up a little. What is showing as 44% becomes 48%. As well, I'm not sure any other way to have the current and previous on the same row as the Sparkline without using the table calc setup.

                                    If I don't use a table-calc I end up with a row for previous, a row for current and a blank row for the third month of the sparkline.

                                     

                                     

                                    I'm trying to understand this.

                                     

                                    I don't know your calcs like you do, and I don't want to reverse engineer them to figure them out.

                                     

                                    Give me an example of what I should be noticing.  For instance, "When I use [x-y-z calc] and filter out "ABC" I see (this amount) when I should see (that amount), and here's why it should be (that amount)..."

                                     

                                    And which ones are "MCA LOD calcs"?

                                    1 2 Previous Next