Standard deviation (Stdev) over multiple dimensions

[#1]

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:

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?

[sunny_talwar]

May be this:

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

[swuehl]

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

[#5 · Accepted solution]

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]

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

[#8]

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]

Right, that's probably the reason.