topic Sorting with Above in App Development

Sorting with Above

agigliotti — Thu, 14 Sep 2023 15:23:43 GMT

Hi,

I have a table like this:

LOAD * INLINE [
Customer, Unit of measure, Quantity variance YoY
A, BO, 18500
A, MQ, 6200
B, KG, 22100
B, MQ, 7400
C, MQ, 4800
C, NR, -4600
D, MQ, -4100
];

The desired result in the UI is a table as below:

Customer - Unit of measure - Quantity variance YoY - Variance accumulated
B - KG - 22100 - 22100
A - BO - 18500 - 40600
B - MQ - 7400 - 48000
A - MQ - 6200 - 54200
C - MQ - 4800 - 59000
D - MQ - -4100 - 54900
C - NR - -4600 - 50300

I tried with the below expression for Variance accumulated:

Rangesum(above(total column(1),0,RowNo(TOTAL)))

on Customer sorting expression:

sum(Quantity variance YoY)

but i got the below table result:

Could someone help me?

Many thanks in advance for your time.

Best Regards

@rwunderlich @Oleg_Troyansky @Anil_Babu_Samineni @marcus_sommer @sunny_talwar @swuehl @Kushal_Chawda

Re: Sorting with Above

Oleg_Troyansky — Thu, 14 Sep 2023 15:31:24 GMT

Well, your formula appears to be working as expected, it does the math correctly. The only difference between the "desired output" and the table is in the sorting order. Your "desired result" appears to be sorted by the variance in descending order, while your table is sorted by Customer and U/M. If you sort your table by the variance, you should see exactly the same picture as in your desired result.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Re: Sorting with Above

agigliotti — Thu, 14 Sep 2023 15:35:28 GMT

How can I sort the table by the variance in the UI?

Re: Sorting with Above

agigliotti — Thu, 14 Sep 2023 15:43:36 GMT

The only way i found to get the desired result is using a calculated dimension (Customer&'-'&Unit of measure) instead of Customer.
But of course i don't want to show the Unit of measure with the customer in the same column...

Re: Sorting with Above

Oleg_Troyansky — Thu, 14 Sep 2023 17:18:01 GMT

I'm a bit confused, not sure if I understand the challenge. Why not just promote the Expression for the variance to have the first priority in the Sorting tab of Properties?

Re: Sorting with Above

agigliotti — Thu, 14 Sep 2023 18:27:24 GMT

Hi @Oleg_Troyansky ,

I just tried to sort numerically descending on variance and make it first in the sorting list but the table sorting is not affected because of Above function in the measure expression.

Re: Sorting with Above

marcus_sommer — Fri, 15 Sep 2023 05:04:02 GMT

I'm not sure if there are (practically) ways to get your wanted expression-results with the intended sorting - without using a calculated dimension. But this dimension mustn't be visible else you could hide it with the options from the presentation tab (at least by using a straight-table or a bar-chart - a pivot does not provide this option).

Re: Sorting with Above

agigliotti — Fri, 15 Sep 2023 09:55:52 GMT

Hi @marcus_sommer ,

I'm pretty agree with you.
Is there some trick to show only a substring (Customer) of the calculated dimension (Customer&'-'&Unit of measure) in a straight table?
Obviously I can't hide it.

Re: Sorting with Above

marcus_sommer — Fri, 15 Sep 2023 10:57:24 GMT

You could hide such calculated dimension:

Re: Sorting with Above

agigliotti — Fri, 15 Sep 2023 12:40:40 GMT

Hi @marcus_sommer ,

I'm using Qlik Sense but anyway if i hide the calculated dimension the table sorting is not correct.

Re: Sorting with Above

marcus_sommer — Fri, 15 Sep 2023 13:09:46 GMT

You may use a dual() with any sub-string you want as string-representation and using a numeric coding of customer + unit - maybe just something like: customer_id * pow(10, 6) + unit_id respectively any logic which returned unique numeric values maybe even an autonumber() of both fields.

Personally I wouldn't do it - at least not for this purpose - else just using the calculated dimension and removing the single dimension because this would contain all wanted information in the right way and everything additionally has just the potential to increase efforts and complexity as well as confusing the users.

Re: Sorting with Above

Oleg_Troyansky — Fri, 15 Sep 2023 13:21:42 GMT

Hi,

Yes, I tested your scenario, and you need the calculated dimension, indeed. You can hide it though, using the Presentation settings. In my testing, I added a third dimension, as a concatenation of the first two dimensions, and I sorted it by expression. Then, I hid the dimension in the Presentation properties.

Cheers,

Re: Sorting with Above

agigliotti — Fri, 15 Sep 2023 14:30:26 GMT

Hi @Oleg_Troyansky

Could you explain what dimensions are you added to the above two?
I'd need to show in the straight table only the customer and unit of measure dimensions.
But the only way to get it working as expected is showing the calculated dimension instead of the customer dimension.

Re: Sorting with Above

Oleg_Troyansky — Fri, 15 Sep 2023 15:17:11 GMT

Yes, certainly!

In my testing, I created a Straight Table with two dimensions and an expression, just like yours. I then added another expression with your formula. As you described, the sorting by the first measure didn't work, because of the Above() function.

So, I added a third dimension, concatenating the first two dimensions. In your case it will be something like this:

Product & '|' & UM

Then, I sorted the chart by this third dimension, sorted by expression, and I listed the same formula as the first expression has (not "column(1)", but rather a full copy of the formula - Sum(Quantity).

Finally, I opened the Presentation properties of the chart and I defined the third expression as Hidden.

That did the trick.

Best,

Re: Sorting with Above

agigliotti — Fri, 15 Sep 2023 15:30:08 GMT

Hi @Oleg_Troyansky ,

I'm using Qlik Sense not QlikView.
However I tried what you told me but when I hide the calculated dimension the table sorting is not correct.

Re: Sorting with Above

Oleg_Troyansky — Fri, 15 Sep 2023 15:44:36 GMT

Well, you got me fooled there, because your screen shot is certainly from QlikView... I don't think this can work in Qlik Sense.

You would need to create this combo field in your data and then you can use it as a dimension in the AGGR function that would calculated the same, without relying on the table dimensions. Something like this:

AGGR(

Rangesum(above(total sum(Quantity),0,RowNo(TOTAL)))

, (ComboDim, ("=sum(Quantity"), Desc))

Quoting from memory, so please check the parentheses and quotes...

Cheers,

Re: Sorting with Above

agigliotti — Fri, 15 Sep 2023 20:17:48 GMT

Hi @Oleg_Troyansky ,

You right the screenshot comes from QlikView.

Unfortunately I can't create the combo field (Customer&Unit_of_measure) in the load script because they are in different tables.
Customer is in the sales fact table while Unit_of_measure is related to the product purchased by the customer.

Re: Sorting with Above

Oleg_Troyansky — Fri, 15 Sep 2023 20:34:41 GMT

Well, it's obviously your solution and your call, but conceptually it's very easy to populate the U/M in the Fact table as a temporary field, create the Combo field that you need, and then drop the field from the Fact.

Re: Sorting with Above

agigliotti — Sat, 16 Sep 2023 09:42:59 GMT

Right, for sure I'll do it in case our client will ask us to remove U/M from the calculated dimension.

Re: Sorting with Above

agigliotti — Mon, 18 Sep 2023 13:53:45 GMT

Hi,

I solved adding two fields using autonumber() for customer_id and unit_id in DLE, then I used the Dual function for the calculated dimension using them for numeric coding.
Many thanks for your help.