How to store the result of a function that returns an array to a variable?

[martin4]

Hi Everyone. I would like to store the result of an Aggr() function to a variable. Is this possible? I believe Aggr() is relatively unique in that it returns an array of values instead of a single value.

As an example, let's say I have a simple table in my data model based on the following code:

Data:
LOAD * INLINE [
Category, Value
a, 1
a, 2
b, 3
b, 4
];

I would like to store the result of =Aggr(Sum(Value), Category) in a variable and then reference that resulting array throughout my dashboard. (I do not want to store that code as text to be evaluated every time I reference it.). I imagine that the result of this function would look something like this if it were a JavaScript array: [{key: "a", value: 3}, {key: "b", value: 7}].

The goal is to only evaluate this function once for the current selections, but then be able to use its results in several different places in the dashboard.

Thanks!

[MarcoWedel]

How would you use this variable in your dashboard?

Can you give an example?

thanks

Marco

[martin4]

Thanks for your response!

This was a simple example, but let's say I store the result in a variable named vAggrResult. I want to know the minimum value in one Text Object (=Min(vAggrResult)), the maximum value in another Text Object (=Max(vAggrResult)), and the values for each category in a straight table (dimension is Category and Expression is =vAggrResult). Instead of calculating the result 3 times total, I would like to just have to calculate it once.

Again this is a simple example, but imagine the Aggr() function taking a second to calculate every time it is evaluated. The real-world example is a complicated function and I want to use it in a line chart, a straight table, and as part of the expression in the charts' titles.

-Martin

[MarcoWedel]

My point is, the creation of this variable might be easier than its usage and its performance even worse than whatever you are trying to optimize.

Having said that, a variable could have a structure assigned, like described in your initial example, using a button with a "set variable" action and a value expression like:

='['&Concat(Aggr('{key: "'&Category&'", value: '&Sum(Value)&'}', Category),',')&']'

This variable however could not be used in expressions like Max(vAggrResult) in the way you seem to intend.

If you have concerns regarding your application performance I think you could instead try different methods like optimizations of your data model and expressions, reduction of data or calculation conditions.

hope this helps

Marco

[rwunderlich]

You could store the result in a list using Concat like:

=Concat(Aggr(Sum(Value), Category) , ',')

And then reference the result in Range functions like:

RangeMax($(vAggrResult))

RangeMin($(vAggrResult))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[martin4]

Thanks. I take your point.

I was hoping that there was a way to store it in a variable as a data structure instead of first converting it to a string using Concat(). I agree it may not be worth it to convert it to a string for storage and then parse it back to a data structure each time I need to reference it.

Thanks,

Martin

[martin4]

Clever idea! Would you have a recommendation for a vlookup-style approach for getting the value of a given category? Thanks!

[marcus_sommer]

I'm not sure if your approach is really expedient because you have already arrays available because the tables within the data-model could be regarded as arrays. And there are a lot of native and simple features to get the wanted data from there.

Of course it's possible to create with (nested) aggr() within or without variables any table/array-construct within the UI but usually it's much more easier within the script as in the UI and mostly you will also get a better usability and performance.

Therefore if you are missing anything within your application you should implement it within the data-model.

- Marcus

[rwunderlich]

In regards to dynamically subsetting the result into categories. I'm going to echo what others have said. At that level, you would be creating complexity of dubious value and likely would make performance worse.  

-Rob

[martin4]

Thanks everyone. I agree that it would be more performant to stage the data as a table in the script. I will definitely go that route if I can figure out a way to do so.