
1. Re: Zscore calculation
Deepak Rai Apr 3, 2018 12:26 PM (in response to John McGuirk)I think you used the same formula earlier also. Do you remember I created an LOD for you on this. Did you try that?

2. Re: Zscore calculation
John McGuirk Apr 3, 2018 12:33 PM (in response to Deepak Rai)Hi Deepak. We were not able to get the crosstab report to do some of the things we wanted to do so we changed the data source from pivoted to unpivoted and started over. The LOD calculation you sent before uses an LOD calculation for a specific field (in the case before “A”). The way we have the data set up now we have just three columns – Date, Spread Name, and Spread. I have been playing around with various LOD calculations but have so far not been able to make it work.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

3. Re: Zscore calculation
Deepak Rai Apr 3, 2018 12:34 PM (in response to John McGuirk)Are you Sure your Manual Calc is CORRECT?

4. Re: Zscore calculation
John McGuirk Apr 3, 2018 12:40 PM (in response to Deepak Rai)Yes, the attached Excel file has the manual calculation showing a z score of 2.001 for CLO BBB data series.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

5. Re: Zscore calculation
Deepak Rai Apr 3, 2018 12:46 PM (in response to John McGuirk)I think The difference is because you have NULL in your Data. You are including them while calculating Average in Excel

6. Re: Zscore calculation
John McGuirk Apr 3, 2018 12:50 PM (in response to Deepak Rai)OK. I will try to make it work the old way then. Thanks.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

7. Re: Zscore calculation
John McGuirk Apr 3, 2018 1:09 PM (in response to Deepak Rai)Actually I just verified that the calculation is correct in Excel. It’s the Tableau calculation that is incorrect.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

8. Re: Zscore calculation
Deepak Rai Apr 3, 2018 1:10 PM (in response to John McGuirk)and the Tableau formula you created is CORRECT?

9. Re: Zscore calculation
John McGuirk Apr 3, 2018 1:15 PM (in response to Deepak Rai)No, it must not be correct. That is why I’m asking the question. I can’t get the calculated field to work in Tableau.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

10. Re: Zscore calculation
Deepak Rai Apr 3, 2018 1:18 PM (in response to John McGuirk)I am Just asking about your Formula. Can you rewrite your formula for Z score so that I can Try in Tableau. Please Rewite your Excel Logic Here what is divided by what

11. Re: Zscore calculation
John McGuirk Apr 3, 2018 1:23 PM (in response to Deepak Rai)Sorry, I had attached the workbook and thought you could see the z score calculation I am currently using. The calculation is (sum()WINDOW_AVG(sum()))/(WINDOW_STDEV(sum())). I had attempted to do an LOD calculation based on date similar to what you had showed me before but I was not successful. I also tried adding ZN to the calculation to exclude null values but that didn’t work either.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

12. Re: Zscore calculation
Deepak Rai Apr 3, 2018 2:44 PM (in response to John McGuirk)what is 275 in your excel formula?

13. Re: Zscore calculation
John McGuirk Apr 4, 2018 6:04 AM (in response to Deepak Rai)275 is the last spread record on the last date of 3/23/18 in the data. So the calculation is 275 minus the average spread of 423.3908 divided by the standard deviation of 67.56191. And I think that is the problem with the Tableau formula I am currently using. I’m not sure how to get it to use the last spread value in the calculation.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759

14. Re: Zscore calculation
John McGuirk Apr 4, 2018 6:09 AM (in response to Deepak Rai)So I am trying to calculate the z score using the last spread value depending on the date selected on the date slider.
John McGuirk
Senior Analyst III  Investment Operations
MEMBERS Capital Advisors, Inc.  CUNA Mutual Group
800.356.2644 x665.6713  (F) 608.236.7759