topic Re: Standard deviation (Stdev) over multiple dimensions in QlikView

Standard deviation (Stdev) over multiple dimensions

Tue, 24 May 2016 15:28:53 GMT

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?

Re: Standard deviation (Stdev) over multiple dimensions

sunny_talwar — Tue, 24 May 2016 15:32:11 GMT

May be this:

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

Re: Standard deviation (Stdev) over multiple dimensions

swuehl — Tue, 24 May 2016 15:35:02 GMT

Or maybe:

Stdev(TOTAL <Team>

Aggr(

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

, Date, Team)

)

Re: Standard deviation (Stdev) over multiple dimensions

sunny_talwar — Tue, 24 May 2016 15:36:43 GMT

Not or, def. this

Re: Standard deviation (Stdev) over multiple dimensions

Wed, 25 May 2016 07:20:25 GMT

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

)

Re: Standard deviation (Stdev) over multiple dimensions

sunny_talwar — Wed, 25 May 2016 08:37:32 GMT

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

Re: Standard deviation (Stdev) over multiple dimensions

swuehl — Wed, 25 May 2016 08:41:51 GMT

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

Re: Standard deviation (Stdev) over multiple dimensions

Wed, 25 May 2016 08:48:21 GMT

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.

Re: Standard deviation (Stdev) over multiple dimensions

swuehl — Wed, 25 May 2016 10:21:17 GMT

Right, that's probably the reason.