Use case for sorted aggr() with nested structured parameter

[levente81]

Hi,

I wonder if anybody has ever faced such situation where using nested structured parameter was the ultimate solution...

I have no clue how to interpret the documentation about this topic:

"(FieldName, (FieldName2, (Sort-type, Ordering)))"

How will FieldName2 impact the sorting of FieldName?

If you have such use case and you are willing to share it, please do not hesitate.

Thanks, and best regards,

Levente

[theoat]

prenons l'exemple d'une entreprise, faisant plusieurs ventes chaque moi. Nous utilisons la fonction ci-dessous afin d'avoir le nombre de vente par mois.

Sum(Aggr(Sum(Sales), (Year, (Numeric, Ascending)), (Month, (Numeric, Ascending)) ))

Les paramètres sont les suivant :
Aggregation is done by month of each year.
The first sort order is on the year, which must be sorted numerically (year is a number) and ascendingly. The second sorting is carried out over the month in the same way. Feel free to look at the photo below to get a better understanding.



Kind regards
Théo ATRAGIE.

[levente81]

Thanks for example, but it does not quite fit to the questioned topic... I would like to see an example where nested structured parameter had to be used.

Your example simply presents how each of multiple dimensions can be sorted in the aggr() expression.

This is what I asked:

"(FieldName, (FieldName2, (Sort-type, Ordering)))"

This is what you responded:

"(FieldName (Sort-type, Ordering))

,

(FieldName2, (Sort-type, Ordering))"

[levente81]

I have just faced that using nested StructuredParameters was the key to finally achieve my goals regarding a pareto coloring "project".

The way they work is still not crystal clear... Here is why:

I intended to have a bit more complex logic then the usual ones you might be able to find with google search for Qlik Sense pareto coloring.

Let's say we have a bar chart where we would like to implement pareto coloring logic.

In this case my goal is to paint with certain color !!!ALL!!! bars that are needed to cover at least 80 percent, and to color the rest of the bars differently.

This difference (between my approach and the "usual ones" that I could find with google search) is only important because it requires a bit more complex condition where you shall be able to maintain consistency between the discrete parts of those conditions that can ensure exactly that outcome what you need. You cannot let remain any ad-hoc (?random?) step...

Let's say that we have a dataset where dim1 has values of a, b and c. They have a measure where the sums are 60,30 and 10 in this case a "simple less then 80" condition would paint only bar for 'a' value of dim1, while my goal is to paint all that are needed to cover at least 80, so in this case I want to get 'pareto color' for 'b' value of dim1.

My ultimate solution for this is coloring each dim1 values where the rolling sum is less then 80% proportionally and only paint one more where the rolling sum achieves or exceeds 80%. This latter condition is ensured with a reverse logic, where I check another rolling sum (bottom to top) - this shall be equal to or higher than 20% proportionally.

So, I need to ensure that execution logics are harmonized within these conditions, and this is where I face huge challenges in aggr() function. I could not find any detailed description, no official material either which explains really thoroughly how aggr() is actually working.

I have created a test data this way:

for i = 1 to 20
	paretodata:
    LOAD * INLINE [
    	dim1, ertek
        a,53 
        b,17
		c,8
        d,4
        e,3
        f,3
        g,3
        h,2
        i,2
        j,2
        k,1
        l,1
        m,1
	];
next i

	paretodata:
    LOAD * INLINE [
    	dim1, ertek
        e,1
        f,0
        g,-1
        h,1
        i,0
        j,-1
	];

This dataset ensures that I have to execute an aggregation on the raw data first and then I shall calculate the rolling sums, which leads me to the necessity to use aggr() - sometimes the use case requires more flexibility then what can be achieved with a hardcoded approach, where you can simply create one or more technical fields in which you can store different sums that fits the project needs.

So, If I need more flexibility, then I have to ignore preparation in data load editor and have to solve the problem dynamically using aggr().

Aggr() is sortable for a while - this feature enables really dynamic solutions by providing developers with sufficient impact on the structure of the phantom table that is constructed temporarily. It is quite easy to sort the temporary dataset by certain dimension in ascending or descending way, but there are use cases in real world that do need more sophisticated expressions, where you need to sort certain dimension by a calculated value in descending order and the same dimension shall further be sorted textually.

I faced challenges during the pareto coloring project, when there were same measure values for certain dimension values, which resulted in a random way how the order of the records were taken into consideration in the temporary aggr() table. So in case when I selected e, f, g, k, l and m values from dim1, then I expected to see e, f, g and k as 'pareto colored' while l and m as being colored with an other color.

For some weird reason Qlik painted the bar for l value of dim1 to be pareto colored so the final chart consisted of altering colors (3 red, 1 green, 1 red, 1 green). I do not know why it happened using simple structured parameters in aggr() function, but when I had this opinion to twist it with this nested approach it started to work properly...

using (dim1, (=sum(ertek),DESC)) in aggr() function

using (dim1, (dim1,(=sum(ertek),DESC))) - is it by accident that I get properly sorted temporary dataset and consequently properly colored chart?

I just do not see how this expression ensures that now K value of dim1 comes first in the temporary table and consequently gets 'pareto color' while L and M get colored differently... does it happen accidentally, or someone can explain step by step what happens behind the scene that results this particular order of colors...

If someone can explain

- what milestones are behind the scenes (during the execution of an aggr() expression),

- when an aggr() expression is executed,

please share with us.