Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can Anyone know on how to write script expressions.
Like given in above link, as I wanted to write a function for Range Median.
Thanks,
Rangam Sheshadri
Customer | Inovoice ID | Shipment Part 1 Qty | Shipment Part 2 Qty | Shipment Part 3 Qty |
A | 1 | 10 | 490 | 100 |
A | 2 | 10 | 490 | 0 |
A | 3 | 10 | 490 | 200 |
A | 4 | 100 | 400 | 0 |
A | 5 | 100 | 400 | 300 |
A | 6 | 100 | 400 | 400 |
A | 7 | 100 | 400 | 100 |
A | 8 | 100 | 400 | 0 |
A | 9 | 55 | 445 | 200 |
A | 10 | 48 | 452 | 0 |
A | 11 | 987 | 13 | 0 |
A | 12 | 23 | 477 | 400 |
A | 13 | 24 | 476 | 100 |
A | 14 | 35 | 465 | 0 |
B | 15 | 10 | 490 | 100 |
B | 16 | 10 | 490 | 0 |
B | 17 | 10 | 490 | 200 |
B | 18 | 100 | 400 | 0 |
B | 19 | 100 | 400 | 300 |
B | 20 | 100 | 400 | 400 |
B | 21 | 100 | 400 | 100 |
B | 22 | 100 | 400 | 0 |
B | 23 | 55 | 445 | 200 |
B | 24 | 48 | 452 | 0 |
B | 25 | 987 | 13 | 0 |
B | 26 | 23 | 477 | 400 |
B | 27 | 24 | 476 | 100 |
B | 28 | 35 | 465 | 0 |
B | 29 | 100 | 400 | 300 |
B | 30 | 100 | 400 | 400 |
B | 31 | 100 | 400 | 100 |
B | 32 | 100 | 400 | 0 |
B | 33 | 55 | 445 | 200 |
B | 34 | 48 | 452 | 0 |
B | 35 | 987 | 13 | 0 |
B | 36 | 23 | 477 | 400 |
B | 37 | 24 | 476 | 100 |
Here is the sample data and below are the expected value:
Customer | Median | Min(Part 1 and 2) | Max(Part 1 and 2) | Avg(Part 1 and 2) | Total Avg |
A | 100 | 10 | 987 | 267.8571429 | 221.4286 |
B | 100 | 10 | 987 | 271.7391304 | 228.9855 |
Load your data using CrossTable load like this
Table: CrossTable ([Shipment Part], Qty, 2) LOAD * INLINE [ Customer, Inovoice ID, Shipment Part 1 Qty, Shipment Part 2 Qty, Shipment Part 3 Qty A, 1, 10, 490, 100 A, 2, 10, 490, 0 A, 3, 10, 490, 200 A, 4, 100, 400, 0 A, 5, 100, 400, 300 A, 6, 100, 400, 400 A, 7, 100, 400, 100 A, 8, 100, 400, 0 A, 9, 55, 445, 200 A, 10, 48, 452, 0 A, 11, 987, 13, 0 A, 12, 23, 477, 400 A, 13, 24, 476, 100 A, 14, 35, 465, 0 B, 15, 10, 490, 100 B, 16, 10, 490, 0 B, 17, 10, 490, 200 B, 18, 100, 400, 0 B, 19, 100, 400, 300 B, 20, 100, 400, 400 B, 21, 100, 400, 100 B, 22, 100, 400, 0 B, 23, 55, 445, 200 B, 24, 48, 452, 0 B, 25, 987, 13, 0 B, 26, 23, 477, 400 B, 27, 24, 476, 100 B, 28, 35, 465, 0 B, 29, 100, 400, 300 B, 30, 100, 400, 400 B, 31, 100, 400, 100 B, 32, 100, 400, 0 B, 33, 55, 445, 200 B, 34, 48, 452, 0 B, 35, 987, 13, 0 B, 36, 23, 477, 400 B, 37, 24, 476, 100 ];
and then on the front end
Dimension
Customer
Expressions
Median(Qty) Min({<[Shipment Part] = {"*Part 1*", "*Part 2*"}>}Qty) Max({<[Shipment Part] = {"*Part 1*", "*Part 2*"}>}Qty) Avg({<[Shipment Part] = {"*Part 1*", "*Part 2*"}>}Qty) Avg(Qty)
I worked on this in QlikView, but it will work the same way in Qlik Sense
Thanks for reply, in the median I need to calculate the media for all three shipment Quantities.
in Qlik only one field is supported for median.
Did you get time to look at the sample I provided?
I have millions of records in table and multiple measures, wherein I have provided sample data to match this scenario, if I load the data using cross table then it will cross 100 Millions records, due to this I wanted to follow this scenario.
Thank you for your reply .
Well, I am not sure if you would be able to find Median across the rows and column at the same time. For Column you have Median() function and for rows you can use RangeMedian(). But not sure if there is a way to combine them. May be somebody else can offer help here.