Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Standard deviation (Stdev) over multiple dimensions

Hi everybody,

I'm facing a bit of difficulty creating a set of expressions that together determine wether or not a revenue line falls between a certain bandwith. This bandwidth that I calculate is the standard deviation times x. If the revenue line falls outside of that specific bandwidth the final expression should give back either a 1 (outside of bandwidth) or a 0 (inside of bandwidth) (see the chart below, red dot). To illustrate this method I've included a screenshot of the chart below:

screenshot_graph.PNG

Now I want to do the same calculations, but combined with a second dimension. The part that I'm stumbling over is the standard deviation function. This is the current expression in the chart above:

Stdev(TOTAL

  aggr(

  sum({<Date={'>=$(vDate.Min)<=$(vDate.Max)'}, _Flag={0}, Check={'$(=MaxString(Check))'}>}Revenue)

  ,

  Date

  )

)

Now say I want to do the same calculation, but in a straight table with a second dimension, say Team. How do I get the Stdev function to work over all the dates in the expression above, but for each individual team?

1 Solution

Accepted Solutions
Not applicable
Author

Thanks @swuehl!

I had to make one last alteration so it doesn't output dates that I don't want in the set expression, but that worked!

Stdev(TOTAL <Team>{<Date={">=$(vDate.Min)<=$(vDate.Max)"}>}

  aggr(

  Sum({<Date={'>=$(vDate.Min)<=$(vDate.Max)'}, _Flag={0}, Check={'$(=MaxString(Check))'}>} Revenue)

  ,

  Date, Team

  )

)

View solution in original post

8 Replies
sunny_talwar

May be this:

Stdev(TOTAL  <Team> Aggr(Sum({<Date={'>=$(vDate.Min)<=$(vDate.Max)'}, _Flag={0}, Check={'$(=MaxString(Check))'}>} Revenue), Date))

swuehl
MVP
MVP

Or maybe:


Stdev(TOTAL <Team>

     Aggr(

          Sum({<Date={'>=$(vDate.Min)<=$(vDate.Max)'}, _Flag={0}, Check={'$(=MaxString(Check))'}>} Revenue)

     , Date, Team)

)

sunny_talwar

Not or, def. this

Not applicable
Author

Thanks @swuehl!

I had to make one last alteration so it doesn't output dates that I don't want in the set expression, but that worked!

Stdev(TOTAL <Team>{<Date={">=$(vDate.Min)<=$(vDate.Max)"}>}

  aggr(

  Sum({<Date={'>=$(vDate.Min)<=$(vDate.Max)'}, _Flag={0}, Check={'$(=MaxString(Check))'}>} Revenue)

  ,

  Date, Team

  )

)

sunny_talwar

I would have thought that since the inner aggregation is restricted, you wouldn't need to again restrict the outer aggregation to get the stdev within your date range. But adding it just in case is never a bad idea.

Would you be able to close this thread by marking your response as correct and may be Stefan's response as helpful.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny

swuehl
MVP
MVP

I think in this case it's necessary to avoid creating zero values for the not-wanted dates that result in a wrong std dev. It wouldn't be a problem for a outer Sum().

But this problem should have also been present in your original expression and setting, right, Maarten?

edit: latter assuming that no team made revenue on a date

Not applicable
Author

Hi Stefan!

The problem didn't occur in the original expression, but I can't really explain why. I guess it's because all the calculations we're done over one dimension and adding a second dimension somehow expanded the possibilities in all the dates in my data.

swuehl
MVP
MVP

Right, that's probably the reason.