Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Median QlikSense

 Hello all,

Below is sample code :

Test :
Load * Inline
[
Year,Region,Values
2018,India,10
2018,SriLanka,20
2018,Australia,32
2019,India,12
2019,SriLanka,15
];

I create a line chart :

Dimension :Year

Measure : Median(Values)

Output is : Year 2018 - 20 ;Year  2019 - 13.5

But i want output like this :

Output is : Year 2018 - 20 ;Year  2019 - 12

The logic should be even though for 2019 we don't have value for Australia we want to consider that in median calculation with zero assigned to it , so after sorting it should be Median of values : 0,12,15 which should be equal to 12.

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

You have to populate your sparsely populated data correctly for that to work.

A sample code to wards that would be like:

t1:
Load * Inline
[
Year,Region,Values
2018,India,10
2018,SriLanka,20
2018,Australia,32
2019,India,12
2019,SriLanka,15
];

t2:
Load Distinct Region Resident t1;
Join (t2)
Load Distinct Year Resident t1;

Left Join (t2)
load * Resident t1;

Drop Table t1;

NULLASVALUE Values;
Set NullValue='0';
NoConcatenate
t3:
Load * Resident t2;
Drop	Table t2;

 

 Capture.PNGCapture2.PNG

View solution in original post

4 Replies
Highlighted
MVP
MVP

You have to populate your sparsely populated data correctly for that to work.

A sample code to wards that would be like:

t1:
Load * Inline
[
Year,Region,Values
2018,India,10
2018,SriLanka,20
2018,Australia,32
2019,India,12
2019,SriLanka,15
];

t2:
Load Distinct Region Resident t1;
Join (t2)
Load Distinct Year Resident t1;

Left Join (t2)
load * Resident t1;

Drop Table t1;

NULLASVALUE Values;
Set NullValue='0';
NoConcatenate
t3:
Load * Resident t2;
Drop	Table t2;

 

 Capture.PNGCapture2.PNG

View solution in original post

Highlighted
Contributor III
Contributor III

Thanks a lot.

Can we do the same based on count of distinct values of Country?

For example

t1:
Load * Inline
[
Year,Region,Values
2018,India,10
2018,SriLanka,20
2018,Australia,32
2019,India,10
2019,SriLanka,15
2019,SriLanka,20
];

 

Here for 2019 , count for India is 1 , SriLanka is 2 and Australia is zero, hence median of 0, 1 and 2 which will be 1.

Highlighted
MVP
MVP

Try:

Median(Aggr(Count(Region), Year, Region))

Highlighted
Contributor III
Contributor III

Hello ,

Thanks a lot.

I have another scenario :

t1:
Load * Inline
[
Year,Region,Impact
2018,India,Low
2018,SriLanka,Low
2018,Australia,Significant
2018,Japan,Low
2019,India,Low
2019,SriLanka,Low
2019,SriLanka,Significant
2019,India,Medium
2019,Australia,Low
2019,India,Significant
2019,UK,Significant
2019,UK,Low
];

Here we need to consider the same year and also Impact equal to Significant

For example in 2018 it will be,
India = 0
Sri Lanka = 0
Australia - 1
Japan - 0

Median will be zero

In 2019,
India - 1 (Impact is equal to Significant in one case)
SriLanka - 1 (Impact is equal to Significant in one case)
Australia - 0 (Impact is equal to Significant in zero case)
UK - 1 (Impact is equal to Significant in one case)

Median for 0,1,1,1 which is 1