How to divide by set expression formula selection

[MPkg]

Hello all,

please help me, it is little bit urgent.

I have a group with two elements:

Group

Element1

Element2.

Each element has several rows with index column and some constants:

Group	index	A	B	C
Element1	1	1	2	3
Element1	2	4	5	6
Element2	3	7	8	9
Element2	4	10	11	12

I need to make a sum of  1 / rangemax(A, B, C) depending on the selection in groups, with specific indexing.

I tried like this:

=1/rangemax(
sum({$<Group=  $:: Group>*<index={1}>}[A]),
sum({$<Group=  $:: Group>*<index={1}>}[B]),
sum({$<Group=  $:: Group>*<index={1}>}[C]))+
1/rangemax(
sum({$<Group=  $:: Group>*<index={2}>}[A]),
sum({$<Group=  $:: Group>*<index={2}>}[B]),
sum({$<Group=  $:: Group>*<index={2}>}[C]))+
1/rangemax(
sum({$<Group=  $:: Group>*<index={3}>}[A]),
sum({$<Group=  $:: Group>*<index={3}>}[B]),
sum({$<Group=  $:: Group>*<index={3}>}[C]))+
1/rangemax(
sum({$<Group=  $:: Group>*<index={4}>}[A]),
sum({$<Group=  $:: Group>*<index={4}>}[B]),
sum({$<Group=  $:: Group>*<index={4}>}[C]))

When I do it like this the chart does not work when I select only Element1 or Element2, but if nothing is selected it works.

What I do not understand is if I change division sign ( / ) with multiplication ( * ), or with ( + ) formula works when Element is selected.

Please help me and BIG thanks!

[marcus_sommer · Accepted solution]

Take a look here Set analysis and set expressions ‒ QlikView by operators to see what each one meant.

Beside this looked your expression more complicated as needed. Maybe it could be simplified with approaches like:

sum(aggr(1/rangemax(A,B,C), Index))

- Marcus

[MPkg]

Thank you very much!!!

If you were here I would take you for several beers!

It seems that I tried to do something that already exists.

[MPkg]

@marcus_sommer 

Hello Marcus,

I am sorry for disturbing you once again, but I have now new problem. I do not know what to do now if I want to have data island of Years:

Years

1

2

3

and want to import it in the aggregation so I can have a result for every year?

This formula I tried does not work:

sum(aggr((Years-1/rangemax(A,B,C))+1, Index,Years))

This looks to me like the problem with aggregation and ValueLoop, it is really hard to solve.

When I do calculation manually year by year it is correct, but in my calculation I will have more that 30 years.

May you please help me?

[marcus_sommer]

Synthetic dimensions could become quite useful to get an advanced navigation and/or selections within the UI but including them within regular objects and expressions is very limited and quite tricky. Especially if not only a single synthetic dimension is needed and/or relations to the normal data-model are created - usually it leads to the creation of a cartesian product and a very worse performance.

Therefore such an approach should be always the last option if no other ways are possible. I suggest a carefully consideration to transfer more logic into the script. For example the rangemax() might be calculated there and/or the crosstable-logic of these measures is transformed into a stream-data-structure and/or some cumulation - I assume that your main-aim behind this is a cumulation - is there done with interrecord-functions like peek() and previous() and/or the Years are connected to an as-of-table. Surely there are even more possibilities.

Beside this you may also check within the UI if your approach really needs an aggr() and/or if the Years logic might be covered with rowno() and/or if you could use interrecord-functions like above() within the chart, too.

- Marcus

[MPkg]

Thank you for quick reply. I really appreciate it.

Yes, I need cumulation based on years, but Years field is not part of any other table. If I had it in my original data I would not need anything additional except aggr of index, Years. But to create all this big data will mean totally different approach, so my current work will, by big part, be in vein. Sum of max(Years) works fine with this kind of aggr, but I cannot find the solution for cumulation.

What I cannot do is to create additional data in script, because rangemax calculates for example:

rangemax(x1*A,x2*B,C),

and x1 and x2 are variables that need to be changeable in the application. I would already create the max of these elements in the original data, but I cannot because of these two variables.

Rowno() I did not use because I inserted index field in the data and used approach to sum data by index, as you offered me with you solution to use aggr. My try to solve this problem, without aggr, would perfectly work with valueloop(1, Years) but I cannot divide by rangemax(sum(..),sum(..),sum(..)). I can multiply, but not divide, and I need division.

I will still look for the solution, but I am really sad, because this problem got to a limitation of the software, so maybe I will not be able to solve it. 

MP

[marcus_sommer]

Try a move from  valueloop(1, Years) to the script with something like:

t: load recno() as Years autogenerate 30;

This creates a loosen table respectively an island table (as far as Years isn't also a field within any other table). Against the synthetic dimensions per valuelist/valueloop it has the benefit that's now a native dimension which could be used as dimension within aggr() or TOTAL statements and might be also grouped in any way which is definitely not possible with valuelist/valueloop.

Beside this take a look of the interrecord-functions from the UI like above - maybe they could be used even if you may need to nest it within the aggr() or even aggr() within aggr() although this could become quite complex and is by larger datasets rather slow.

Further you may consider to create n help-columns, for example to use rowno() or any other counter-method within a column and the next column grabs these values (maybe also for other calculation-parts). The reference could be made per expression-label or column(index). It's an Excel like approach and a bit ugly within Qlik but in some cases it could be helpful (by using a straight-table such columns could be also hidden).

- Marcus

[MPkg]

I tried everything, and there is no way to do this without the change in the data.

Final solution for this is to add field Years and repeat the data the number of times I need. Like this:

Years	Group	index	A	B	C
1	Element1	1	1	2	3
1	Element1	2	4	5	6
1	Element2	3	7	8	9
1	Element2	4	10	11	12
2	Element1	1	1	2	3
2	Element1	2	4	5	6
2	Element2	3	7	8	9
2	Element2	4	10	11	12
3	Element1	1	1	2	3
3	Element1	2	4	5	6
3	Element2	3	7	8	9
3	Element2	4	10	11	12

After this it is only needed to add one more StructuredParameter:

sum(aggr((Years-1/rangemax(A,B,C))+1, Index,Years)).

Only thing I am left to do now is to find a way to add field Years, and copy the data specific number of times, inside of QS load script, so I do not change the original data.

Thank You very much Marcus, You were the most helpful and really saved me!

MP