Nested Functions in Set Analysis

[#1]

Hi all,

This question may be difficult to explain, but I'll do my best!

There's a chart I've developed in one of my reports (a usage report of a given tool) that displays stacked bars for a given area of focus (there are three) with each portion of the bar representing a certain decile of utilization.

So I was able to construct a simple expression using set analysis:

count( {$< [Total Activity] = {">$(#v0)<=$(#v1)"}>}  [User ID])

Where [Total Activity] was the sum of a given user's amount of sessions.

v0 was the bottom end of the decile =max([Total Activity])*0 )

v1 was just the first demarcation point =round(max([Total Activity])*.25, 1)

And all that worked fine, but then my data source (and subsequently my model) changed resulting in me losing the [Total Activity] field.

My new data source is actually a binary load of another QV report; and that QV report calculates its "Total Activity" field by simply doing a count([Session Time]).

So now, I've been able to duplicate my v0 and v1 like so

v1... =round(max(aggr(count([Session Time]), User))*.25, 1)

And now, I'm not really sure how to advance.

I just can't quite get a new expression to meet my needs. I've tried plenty of different variations. I'm not sure I'm properly executing the count within the count.

This is one of my more recent attempts that basically highlights what I'm looking to do.

Count({$<[count([Session Time]]={">$(=$(v0))<=$(=$(v1))"}>}[[User ID]])

So, does anyone have any ideas on how to handle this mess?

Thank you so much!

Dan

[jonathandienst]

Daniel

You cannot use an expression on the LHS of a set expression, this must be a field. Depending on your model, you may be able to use an advanced search function like:

Count({$<[User ID] = {"count([Session Time])>$(=$(v0)) And count([Session Time])<=$(=$(v1))"}>} [User ID])

or else you will need to re-create Total Activity field after the binary load.

HTH

Jonathan

[#3]

Hi Jonathan,

Thanks for the heads up on that!

Sad to report that the advanced search function was unsuccessful as well.

I'm actually not very familiar with Binary Loads beyond just loading the data, is there a way to add the Total Activity field into the script?

Thanks!

Dan

[jonathandienst · Accepted solution]

Hi

The binary load must be the first statement in the script, but there are no limitations after the binary load, so you can resident load data and add additional data sources in the normal way. I would need more information about your data to me more specific, but in principle it should be possible to calculate something like Total Activity from the binary loaded data (using load resident) and join that to the data set.

Regards

Jonathan

[#5]

Well I've gotten it to work by using a resident load! Thank you so much for all your help, Jonathan!