Average Calculation less than Trend Points during time period

jtrollinger
jtrollinger Member
in Cards, Dashboards, Stories

Why is my Average Calculation of 2.84 less than all of the Trend Points.

image.png
image.png

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Your cards seem to agree on an average punch score of 2.84-2.85. Eyeballing your table card, this appears to be correct, because most of your tickets are in the 2-3 range, with less than 500/9K over 4.

    That narrows the problem to the line graph.

    • I think what Ashleigh was suggesting that If you replace Superintendent with your StatusDate field on this table card (no aggregation) and switch to 'by month', you should be able to get a table version of your line graph. Does that table version agree with your line graph, or is closer to the 2.8 range of values we'd expect?
    • Depending on where the values fall, it would be theoretically possible to get monthly averages that are higher than the overall average (see example at bottom). This seems unlikely based on the size of your data, but if you wanted better visibility, you could switch your table card to a pivot table, and add the statusdate to the columns field (switching the graph to 'by month'). Then you could see the punch date by month by supervisor and see if there are any weird things going on in the values by month. (Only Case Coventry punched tickets in October, say)
    • I'd also be curious to see the line graph adding "Superintedent" as the series, so you can see if their individual punch scores are averaging in the same way as the pivoted table card
    • Since it's the line graph card that appears to disagree, it's probably worth double checking your filters on that card one more time, and that you're using the same BeastMode on that card as the others
    • Since your calculation is using "count distinct" in the denominator and numerator, it could also be theoretically possible for the monthly and total averages to be different than the monthly averages if the "distinctness" is distributed differently across months than it is across supervisors:
    image.png

    In this example dataset, the count(distinct Sales)/count(distinct Weight) would be 2/1 for each supervisor and 1/3 for each month (very different!). The overall average would be 2/3, another very different average that appears to disagree with the by supervisor and by month trends.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • MichelleH
    MichelleH Coach

    @jtrollinger Can you please share some more details on how your cards are set up? It's difficult to give suggestions without understanding your data

  • jtrollinger
    jtrollinger Member

    All cards you see are using the same data set with the same filters.

    Here are the two Beast Modes. After seeing both these side by side I think I see the answer but would like to hear your thoughts.

    BEAST MODE for PUNCH SCORE =

    10 * (COUNT(DISTINCT SalesOrderNo) / COUNT(DISTINCT sonum_wtnumber))

    BEAST MODE for Score AVG =

    COUNT(DISTINCT CASE WHEN HdrParentItemCode = 'ZPUNCH' AND JT158_WTParent = 'Y' THEN sonum_wtnumber END)

    /

    COUNT(DISTINCT CASE WHEN JT158_WTParent = 'Y' THEN SalesOrderNo END)

  • MichelleH
    MichelleH Coach

    @jtrollinger Since you're using two beast modes with different definitions it's not realistic to expect them to match. Unless they were written using assumptions specific to your business, they don't seem to be comparable.

  • jtrollinger
    jtrollinger Member

    That's what I thought you were going to say. Let me try and reconcile the difference between the two.

  • jtrollinger
    jtrollinger Member

    My bad, This is the ONLY BEAST MODE BEING USED FOR Gibson Score Trend, Score Trend and SCORE AVG

    BEAST MODE for PUNCH SCORE =

    10 * (COUNT(DISTINCT SalesOrderNo) / COUNT(DISTINCT sonum_wtnumber))

    Again Cards are pulling from the same data set a filtered the same using only the beast mode above.

  • Ashleigh
    Ashleigh Coach

    @jtrollinger does either of your cards have anything in the sort option? Sometimes that can mess with stuff. Also, check if your 2.84 card is filtering by anything else directly in analyzer.

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • Ashleigh
    Ashleigh Coach

    it may be useful to show the screenshot of the cards from the analyzer view.

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • jtrollinger
    jtrollinger Member

    Would someone be willing to jump on a TEAMS / WEBX to walk through this. I have provided all the information I possibly can but this issue would best be solved via talking it out with someone. Trying to type out the problem and explain the issue can only go so far on this forum.

  • jtrollinger
    jtrollinger Member

    Here are screen shots of Cards Filtered:

    • ALL coming from same dataset
    • all using PUNCH SCORE BEAST MODE = 10 * (COUNT(DISTINCT SalesOrderNo) / COUNT(DISTINCT sonum_wtnumber))
    • ALL Using Last 12 Weeks
    image.png
    image.png
    image.png

  • Ashleigh
    Ashleigh Coach

    @jtrollinger Couple things to check first to narrow out a few options:

    1. Can you open up each date field where it says Last 12 weeks and make sure each one is using the same column and that all settings are the same. Try changing the by month to different options just to see how the numbers change.
      image.png
    2. Remove the sort from the table card to see if that changes anything.
    3. Change the table card to have the first column be Status Date and see if it replicates the same number as the graph, it could be since you are comparing two different things. One is an avg of months and the other is an avg by people. The people have way lower numbers then the months do.

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • jtrollinger
    jtrollinger Member
    • All Cards are using the Status Date as the Date Range Field.
    • Removing the SORT Field PUNCH SCORE from the Table does nothing other than resort the table in a different manner. The Total PUN SCORE remains the same.
    • Replacing SUPERINTENDENT with Status date does nothing to the total Line. (I require Superintendent to be a column so I can see individual scores.
    • Adding Status Date to be included with Superintendent Column does nothing to total Line. I also disagree with your statement "The people have way lower numbers then the months do. See Count of Status Date compared to Total number of #PUN Tickets (sonum_wtnumber in BEAST MODE)"
    image.png

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Your cards seem to agree on an average punch score of 2.84-2.85. Eyeballing your table card, this appears to be correct, because most of your tickets are in the 2-3 range, with less than 500/9K over 4.

    That narrows the problem to the line graph.

    • I think what Ashleigh was suggesting that If you replace Superintendent with your StatusDate field on this table card (no aggregation) and switch to 'by month', you should be able to get a table version of your line graph. Does that table version agree with your line graph, or is closer to the 2.8 range of values we'd expect?
    • Depending on where the values fall, it would be theoretically possible to get monthly averages that are higher than the overall average (see example at bottom). This seems unlikely based on the size of your data, but if you wanted better visibility, you could switch your table card to a pivot table, and add the statusdate to the columns field (switching the graph to 'by month'). Then you could see the punch date by month by supervisor and see if there are any weird things going on in the values by month. (Only Case Coventry punched tickets in October, say)
    • I'd also be curious to see the line graph adding "Superintedent" as the series, so you can see if their individual punch scores are averaging in the same way as the pivoted table card
    • Since it's the line graph card that appears to disagree, it's probably worth double checking your filters on that card one more time, and that you're using the same BeastMode on that card as the others
    • Since your calculation is using "count distinct" in the denominator and numerator, it could also be theoretically possible for the monthly and total averages to be different than the monthly averages if the "distinctness" is distributed differently across months than it is across supervisors:
    image.png

    In this example dataset, the count(distinct Sales)/count(distinct Weight) would be 2/1 for each supervisor and 1/3 for each month (very different!). The overall average would be 2/3, another very different average that appears to disagree with the by supervisor and by month trends.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • jtrollinger
    jtrollinger Member

    David,

    Ultimately I found this portion below of your answer to be true. Thank you to all for responding to this question.

    DAVID's Reponse:

    • Since your calculation is using "count distinct" in the
      denominator and numerator, it could also be theoretically possible for
      the monthly and total averages to be different than the monthly averages
      if the "distinctness" is distributed differently across months than it
      is across supervisors:
    image.png

    In this example dataset, the count(distinct Sales)/count(distinct
    Weight) would be 2/1 for each supervisor and 1/3 for each month (very
    different!). The overall average would be 2/3, another very different
    average that appears to disagree with the by supervisor and by month
    trends.

Categories