12 Replies Latest reply on May 4, 2017 4:26 PM by Debasish PaiMazumder

    Calculation on multidimensional array

    Debasish PaiMazumder

      Hi

      I am little confused when I work with multidimensional array in tableau specially during "create calculated field".

      For example- I have a variable x with dimension n = 5 X m = 92.

      I would like calculate how many time x>a over m (=92) for each n and then calculate the average over n.

       

      How do I perform this calculation?

       

      with regards

      -Deb

        • 1. Re: Calculation on multidimensional array
          Justin Larson

          Your description is a little too abstract for me to follow with much specificity, so a mockup of your data would go a long way.

           

          That said, it sounds like you are trying to get an aggregate, then average the result of that aggregate, which is a window calculation. Those are one of the more advanced concepts in Tableau and often get tripped over, and guiding someone through them without a visual aid would be nightmarish.

           

          Can we get a sample workbook mocked up that represents your data and the closest you've been able to get to your intended result?

          • 2. Re: Calculation on multidimensional array
            Debasish PaiMazumder

            Hi Justin,

             

            Thanks for your response. Here is an example (attached).

            Here variable "values" is two dimensional array where dimensions are  member = 5 X date = 92

            I would like calculate new calculate field where "value">90 over 92 days for each member and then calculate the average over all 5 members.

            How do I perform this calculation?

             

            with regards

            -Deb

            • 3. Re: Calculation on multidimensional array
              Zhouyi Zhang

              Hi, Deb

               

              what you need to is just create a calculation field as below

               

              IF {INCLUDE DATETRUNC('day',[date]),[Member]:AVG([values])} >90 THEN

                   1

              END

               

               

              ZZ

              • 4. Re: Calculation on multidimensional array
                Debasish PaiMazumder

                Zhouyi Zhang Thanks for your response. It works. It calculate a field where "value">90 over 92 days for each member. How do I calculate two new fields based on last field - average of all five members/standard deviation of all five members?

                 

                with regards

                -Deb

                • 5. Re: Calculation on multidimensional array
                  Justin Larson

                  Ok, your description does make a lot more sense now that I'm looking at the data.

                   

                  For starters, you can just create a calculation IsOver90:

                  [values]>90

                   

                  which will evaluate, row by row, if the condition is true and return True/False for each row. You can even preview results in the Data Preview pane:

                   

                  This calculation can then be referenced by other calculations, or brought into the view to separate values over 90 from those under, or you can bring it into the filter to remove all values under or over 90.

                   

                  From this vantage point, regular aggregations will probably satisfy your needs. So for example, if you want to take the average of values that were over 90 by member, you would drop avg(values) and members into view and IsOver90 into filter, and filter for true.

                   

                  Another way to represent that would be through another custom calculation like:

                  avg(if [IsOver90] then [values] end)

                   

                  In this one calculation, it returns the value from the [values] column row by row, but only for those over 90, then averages the result. This calculation will work at any level of detail, so you can bring that into the view with member and it will effectively do the same thing.

                   

                  Similarly, if you want to see what percentage of values were over 90, you could write a calculation like:

                  avg(if [IsOver90] then 1 else 0 end)

                  • 6. Re: Calculation on multidimensional array
                    Debasish PaiMazumder

                    Justin Larson Thanks for your response.

                    avg(if [IsOver90] then 1 else 0 end) provides 1 and 0 according to the condition (>90)

                    I have create a new calculation - SUM(if [IsOver90] then 1 else NULL end) to get total number of days it satisfies the condition (>90) for each member. When I drag the calculation on the sheet with member in filter, I am either getting values for individual member/ total number of days for all five members.

                     

                    My actual goal is to create two new variables based on SUM(if [IsOver90] then 1 else NULL end)-

                    1. average of all five members

                    2. standard deviation of all five members

                     

                    How do I create those calculation?

                     

                    Please let me know if you need any further info.

                     

                    with regards

                    -Deb

                    • 7. Re: Calculation on multidimensional array
                      Justin Larson

                      An example of what visualization you would like to create might help clarify your scenario.

                       

                      If you take members off the view, the aggregation will work over all the records in scope. so with Members in rows, IsOver90 in filter, and AVG([values]) will get you the average value for values over 90 by member. Taking the Member pill off the view would get you total avg overall.

                       

                      Standard Deviation is also a built in aggregate, so just dragging [values] into the view, clicking on the green pill, and selecting Measure>St. Dev will change it from sum or avg to st.dev.

                       

                      Are you needing to perform a calculation of the standard deviation of a single member compared to the standard deviation across the whole dataset?

                      • 8. Re: Calculation on multidimensional array
                        Debasish PaiMazumder

                        Justin Larson Thanks again

                        I have provided a sample data for one US county. I would like to generate spatial map for all counties (see attached)

                        I have a temperature data for 92 days from 5 different members. I would like to calculate, for each member, How many days are >90.

                        for example my results is

                        member 1   20days

                        member2    21 days

                        member3   10days

                        member4    41 days

                        member 5   25days

                         

                        I would like to plot average of all members [average = (20+21+10+41+25)/5 =23.4] and standard deviation (STDEV( 20, 21, 10, 41, 25) = 11.29).

                        hope it clarifies your question. Let me know.

                         

                        with regards

                        -Deb

                         

                         

                        Screen Shot 2017-05-04 at 2.11.17 PM.png

                        • 9. Re: Calculation on multidimensional array
                          Justin Larson

                          Now we're getting specific!

                           

                          So what you are doing is a two-step aggregate. First, count how many days were above 90, then take the average / stdev of that figure, when calculated for each member.

                           

                          The counting of days needs to happen at the level of detail of Member, but then the secondary calculation happens over all the members, so an LOD calculation is what is needed here:

                           

                          { FIXED [Member]:countd(if [IsOver90] then [date] end)}

                           

                          The innermost part of this should look familiar. Row by row, if [values]>90 show the date. Then the aggregate counts how many distinct dates appear that meet the criteria. FIXED [Member] tells the calculation to perform for each unique [Member] in the dataset.

                           

                          Then you can take the avg and stdev of this calculation on the view itself by setting the measure on the pill.

                           

                          I'm getting different # of days results from the example you mapped out above, so the avg and stdev are different, but if you bring Member into this view, you can see the math all happening.

                           

                          • 10. Re: Calculation on multidimensional array
                            Debasish PaiMazumder

                            Justin Larson. Thanks Justin. It works but when I add date in filter and I am trying to change the range of date so that calculation will happen on different number of days (instead of 92), It doesn't work. It only shows the results from 92 days.

                             

                            with regards

                            -Deb

                            • 11. Re: Calculation on multidimensional array
                              Justin Larson

                              Try this, on the filter pill for date, select Add to Context. This will cause the filter to be applied before the LOD calculation.

                              • 12. Re: Calculation on multidimensional array
                                Debasish PaiMazumder

                                Justin Larson

                                Thank you so much Justin. It works. I have also another dimension that is US counties, So I used following formula -

                                { FIXED [Counties],[Mem]:countd(if [IsOverThreshold] then [date] end)}

                                 

                                Thanks again

                                -Deb