Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
rangam_s
Contributor II

How to Write Script Expreesion

Hi All,

 

Can Anyone know on how to write script expressions.

 

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/script-exp...

 

Like given in above link, as I wanted to write a function for Range Median.

 

Thanks,

Rangam Sheshadri

Labels (2)
9 Replies

Re: How to Write Script Expreesion

Can you share some sample data with the output you are expecting to see to help you better?
rangam_s
Contributor II

Re: How to Write Script Expreesion

CustomerInovoice IDShipment Part 1 QtyShipment Part 2 QtyShipment Part 3 Qty
A110490100
A2104900
A310490200
A41004000
A5100400300
A6100400400
A7100400100
A81004000
A955445200
A10484520
A11987130
A1223477400
A1324476100
A14354650
B1510490100
B16104900
B1710490200
B181004000
B19100400300
B20100400400
B21100400100
B221004000
B2355445200
B24484520
B25987130
B2623477400
B2724476100
B28354650
B29100400300
B30100400400
B31100400100
B321004000
B3355445200
B34484520
B35987130
B3623477400
B3724476100

 

Here is the sample data and below are the expected value:

CustomerMedianMin(Part 1 and 2)Max(Part 1 and 2)Avg(Part 1 and 2)Total Avg
A10010987267.8571429221.4286
B10010987271.7391304228.9855

Re: How to Write Script Expreesion

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)

image.png

I worked on this in QlikView, but it will work the same way in Qlik Sense

rangam_s
Contributor II

Re: How to Write Script Expreesion

Thanks for reply, in the median I need to calculate the media for all three shipment Quantities.

Re: How to Write Script Expreesion

Okay, so what is the issue?
rangam_s
Contributor II

Re: How to Write Script Expreesion

in Qlik only one field is supported for median.

Re: How to Write Script Expreesion

Did you get time to look at the sample I provided?

rangam_s
Contributor II

Re: How to Write Script Expreesion

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 .

Re: How to Write Script Expreesion

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.