Sorting with Above

[agigliotti]

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 

[marcus_sommer · Accepted solution]

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.

[Oleg_Troyansky]

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,

[agigliotti]

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.

[Oleg_Troyansky]

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,

[agigliotti]

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.

[Oleg_Troyansky]

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,

[agigliotti]

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.

[Oleg_Troyansky]

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.

[agigliotti]

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

[agigliotti]

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.