Forum Discussion

bestep420's avatar
bestep420
Regular Visitor
8 years ago

Standard Deviation for Count By Month

Hey, I'm trying to do something that should be seemingly simple.  I have a data set of records.  I'm trying to get the standard deviation of the count by week, into a line chart.  This formula is off:

PhoneSD = STDEVX.P(Query1, CALCULATE(COUNTAX(FILTER(Query1, Query1[Type]<>"WEB"),Query1[ContactPhone])))

It wants a row level scalar value to utlize as opposed to an aggregated count, so I don't think the function will work in this case.

 

I got the Average easily with the "Average Line" section, just used a measure containing this forumla:

InboundPhone = COUNTAX(FILTER(Query1, Query1[Type]<>"WEB"),Query1[ContactPhone])

 

But Standard Deviation of Count is a little more tough.  I'm doing a simple line chart with the count of records by week.  The average line was easy now just need the standard deviation lines

 

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi bestep420,

     

    Maybe you can try to use allselected funciton on your formualto let it works on total row.

    PhoneSD =
STDEVX.P (
    ALLSELECTED ( 'Query1' ),
    CALCULATE (
        COUNTAX (
            FILTER ( ALLSELECTED ( Query1 ), Query1[Type] <> "WEB" ),
            Query1[ContactPhone]
        )
    )
)

     

    If above not help, can you please provide some detail content to help us clarify your scenario?

     

    Regards,

    Xiaoxin Sheng

    • bestep420's avatar
      bestep420
      Regular Visitor
      8 years ago

      I'll try that also, this formula works for a single bar chart:

       

      PhoneSD = STDEVX.P(

      SUMMARIZE(Query1,Query1[Type],Query1[Week], "Phone Count",COUNTX(FILTER(Query1,Query1[Type]<>"Web"),Query1[ContactPhone]))

      ,MAX([Phone Count]

      )

      )

       

       

      The only problem is I need to to go on this chart and the week is killing the standard deviation line.  In qlikview I'd just tell the formula to ignore week and it would work but can't figure out how to do that here.

       

      SD is zero

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi bestep420,

         

        Perhaps you can share a pbix file, I will test on it. (if I can find some effective suggestions to resolve it)

         

        Regards,

        Xiaoxin Sheng