
1. Re: Calculation on multidimensional array
Justin Larson May 3, 2017 4:08 PM (in response to Debasish PaiMazumder)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 May 3, 2017 4:33 PM (in response to Justin Larson)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

test_cal.twbx 40.9 KB


3. Re: Calculation on multidimensional array
Zhouyi Zhang May 3, 2017 10:17 PM (in response to Debasish PaiMazumder) 
4. Re: Calculation on multidimensional array
Debasish PaiMazumder May 4, 2017 8:29 AM (in response to Zhouyi Zhang)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 May 4, 2017 11:33 AM (in response to Debasish PaiMazumder)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)

test_cal.twbx 38.7 KB


6. Re: Calculation on multidimensional array
Debasish PaiMazumder May 4, 2017 12:24 PM (in response to Justin Larson)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 May 4, 2017 12:45 PM (in response to Debasish PaiMazumder)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?

test_cal.twbx 42.5 KB


8. Re: Calculation on multidimensional array
Debasish PaiMazumder May 4, 2017 1:12 PM (in response to Justin Larson)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

9. Re: Calculation on multidimensional array
Justin Larson May 4, 2017 2:57 PM (in response to Debasish PaiMazumder)Now we're getting specific!
So what you are doing is a twostep 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.

test_cal.twbx 49.8 KB


10. Re: Calculation on multidimensional array
Debasish PaiMazumder May 4, 2017 3:37 PM (in response to Justin Larson)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

test_cal.twbx 50.7 KB


11. Re: Calculation on multidimensional array
Justin Larson May 4, 2017 3:46 PM (in response to Debasish PaiMazumder)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 May 4, 2017 4:26 PM (in response to 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