Styjun

Hiking with a mule or two?

What do these pins mean? Where should I plug them in?

Norwegian refuses EU delay (4.7 hours) compensation because it turned out there was nothing wrong with the aircraft

Install specific version and arch, without specifying the release

How do rulers get rich from war?

Wired to Wireless Doorbell

What was the deeper meaning of Hermione wanting the cloak?

How is underwater propagation of sound possible?

Could someone please show me the steps of this sum?

Why are some of the Stunts in The Expanse RPG labelled 'Core'?

Why there so many pitch control surfaces on the Piaggio P180 Avanti?

How is the problem, ⟨G⟩ in Logspace?

Are actors contractually obligated to certain things like going nude/ Sensual Scenes/ Gory Scenes?

I reverse the source code, you negate the output!

Why do different vector<bool> elements share the same address?

Should the pagination be reset when changing the order?

Is there an in-universe reason Harry says this or is this simply a Rowling mistake?

How can I prevent soul energy from dissipating?

Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

Are there hydrocarbons on the Moon?

Did HaShem ever command a Navi (Prophet) to break a law?

Is it true that, "just ten trading days represent 63 per cent of the returns of the past 50 years"?

Asking an expert in your field that you have never met to review your manuscript

pandas aggregate with dynamic column names

Qliksense: Compute median of grouped data

How to embed QlikSense app in web?Cumulative distinct count across multiple dimensions in QlikSenseQliksense Date Comparison Errorinstallation issue for Qliksense on my Windows 7QlikSense ErrorFetch Current Month Sales in QliksenseQliksense nodes are off randomly on QMCQliksense ::calculated dimensionQlikSense choosing amount of listed item

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I'm facing an issue in QlikSense, trying to compute some statistical indicators (Percentiles, Quartiles, StdDev, Median etc.) on a dataset which is already grouped by the source.

I mean that my dataset is something similar to the following, in which I have for each combination of Week and Customer Age the total number of purchases:

I want to show the median of Customer Age, and due to the structure of the dataset I can't use fractile or median built-in functions, since they would come out with something different.

Let's suppose I want to calculate the median age of people for all the 3 weeks, so that I want to know what's the age of people who have done the 50% of my purchases.

To let you better understand the question, I show you the histogram:

In this case, the median I want to get is 24-26 years, since the 50% of the total population falls under that range.

I found a useful reference here, but I am having troubles in writing this formula in QlikSense

https://mba-lectures.com/statistics/descriptive-statistics/603/relationship-between-quartiles-decile...

Thanks a lot in advance.

[EDIT]: This is my Data Model View:

[EDIT 2]: Here is my qvf with a dataset more similar to the original one I'm using. As you can see, I can't get the correct result using your formula. In addition, I would like to use it in order to plot the trend of the median through weeks, but it doesn't seem to be possible (Even if I use the modified version of the formula I pointed out in the comments).

qliksense

share|improve this question

edited Apr 8 at 9:24

FVerg

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

add a comment
 | 

1

I'm facing an issue in QlikSense, trying to compute some statistical indicators (Percentiles, Quartiles, StdDev, Median etc.) on a dataset which is already grouped by the source.

I mean that my dataset is something similar to the following, in which I have for each combination of Week and Customer Age the total number of purchases:

I want to show the median of Customer Age, and due to the structure of the dataset I can't use fractile or median built-in functions, since they would come out with something different.

Let's suppose I want to calculate the median age of people for all the 3 weeks, so that I want to know what's the age of people who have done the 50% of my purchases.

To let you better understand the question, I show you the histogram:

In this case, the median I want to get is 24-26 years, since the 50% of the total population falls under that range.

I found a useful reference here, but I am having troubles in writing this formula in QlikSense

https://mba-lectures.com/statistics/descriptive-statistics/603/relationship-between-quartiles-decile...

Thanks a lot in advance.

[EDIT]: This is my Data Model View:

[EDIT 2]: Here is my qvf with a dataset more similar to the original one I'm using. As you can see, I can't get the correct result using your formula. In addition, I would like to use it in order to plot the trend of the median through weeks, but it doesn't seem to be possible (Even if I use the modified version of the formula I pointed out in the comments).

qliksense

share|improve this question

edited Apr 8 at 9:24

FVerg

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

add a comment
 | 

I'm facing an issue in QlikSense, trying to compute some statistical indicators (Percentiles, Quartiles, StdDev, Median etc.) on a dataset which is already grouped by the source.

I mean that my dataset is something similar to the following, in which I have for each combination of Week and Customer Age the total number of purchases:

I want to show the median of Customer Age, and due to the structure of the dataset I can't use fractile or median built-in functions, since they would come out with something different.

Let's suppose I want to calculate the median age of people for all the 3 weeks, so that I want to know what's the age of people who have done the 50% of my purchases.

To let you better understand the question, I show you the histogram:

In this case, the median I want to get is 24-26 years, since the 50% of the total population falls under that range.

I found a useful reference here, but I am having troubles in writing this formula in QlikSense

https://mba-lectures.com/statistics/descriptive-statistics/603/relationship-between-quartiles-decile...

Thanks a lot in advance.

[EDIT]: This is my Data Model View:

[EDIT 2]: Here is my qvf with a dataset more similar to the original one I'm using. As you can see, I can't get the correct result using your formula. In addition, I would like to use it in order to plot the trend of the median through weeks, but it doesn't seem to be possible (Even if I use the modified version of the formula I pointed out in the comments).

qliksense

share|improve this question

edited Apr 8 at 9:24

FVerg

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

qliksense

qliksense

share|improve this question

edited Apr 8 at 9:24

FVerg

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

share|improve this question

edited Apr 8 at 9:24

FVerg

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

asked Mar 28 at 14:51

FVergFVerg

2366 bronze badges

1 Answer
1

active

oldest

votes

1

If you want to calculate median in such a scenario you need to weighted median and basically check which dimension value is in the middle:

Aggr(
 If(
 (Rangesum(
 Above([# Purchases],0,RowNo())
 )
 /Sum(TOTAL [# Purchases]))>=0.5 
 and 
 (Rangesum(
 Above([# Purchases],1,RowNo()-1))
 /Sum(TOTAL [# Purchases]))<0.5
 ,[Customer Age])
 ,[Customer Age])

share|improve this answer

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Hubert. Unfortunately your script does not seem to work, returning a null value both in a table and in a KPI chart. It seems correct to me, but doesn't return the expected value.
  
  – FVerg
  Mar 29 at 15:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi @FVerg I put my qvf file with example on my onedrive 1drv.ms/u/s!Am4ZTQ8qcLwRs35Ms4wM8cEnIBxP
  
  – Hubert Dudek
  Mar 30 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ciao Hubert. Your qvf file works like a charm. If I try to replicate your formula on the dataset I showed in the first post, I don't get the same result. Putting the RangeSums in a table (With CustomerAge and # Purchases) it returns me a 0 for every row. I think that's the problem, but I don't get why it happens. Do you know why?
  
  – FVerg
  Apr 1 at 6:18
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please send screenshot of data model viewer so maybe I will figure out something.
  
  – Hubert Dudek
  Apr 1 at 7:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Done. If I use Sum([# Purchases]) in the RangeSums it seems to be working, but I still don't get why.
  
  – FVerg
  Apr 1 at 9:04
  

  
  
  
  

 | 
show 1 more comment

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55400597%2fqliksense-compute-median-of-grouped-data%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

If you want to calculate median in such a scenario you need to weighted median and basically check which dimension value is in the middle:

Aggr(
 If(
 (Rangesum(
 Above([# Purchases],0,RowNo())
 )
 /Sum(TOTAL [# Purchases]))>=0.5 
 and 
 (Rangesum(
 Above([# Purchases],1,RowNo()-1))
 /Sum(TOTAL [# Purchases]))<0.5
 ,[Customer Age])
 ,[Customer Age])

share|improve this answer

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Hubert. Unfortunately your script does not seem to work, returning a null value both in a table and in a KPI chart. It seems correct to me, but doesn't return the expected value.
  
  – FVerg
  Mar 29 at 15:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi @FVerg I put my qvf file with example on my onedrive 1drv.ms/u/s!Am4ZTQ8qcLwRs35Ms4wM8cEnIBxP
  
  – Hubert Dudek
  Mar 30 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ciao Hubert. Your qvf file works like a charm. If I try to replicate your formula on the dataset I showed in the first post, I don't get the same result. Putting the RangeSums in a table (With CustomerAge and # Purchases) it returns me a 0 for every row. I think that's the problem, but I don't get why it happens. Do you know why?
  
  – FVerg
  Apr 1 at 6:18
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please send screenshot of data model viewer so maybe I will figure out something.
  
  – Hubert Dudek
  Apr 1 at 7:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Done. If I use Sum([# Purchases]) in the RangeSums it seems to be working, but I still don't get why.
  
  – FVerg
  Apr 1 at 9:04
  

  
  
  
  

 | 
show 1 more comment

1

If you want to calculate median in such a scenario you need to weighted median and basically check which dimension value is in the middle:

Aggr(
 If(
 (Rangesum(
 Above([# Purchases],0,RowNo())
 )
 /Sum(TOTAL [# Purchases]))>=0.5 
 and 
 (Rangesum(
 Above([# Purchases],1,RowNo()-1))
 /Sum(TOTAL [# Purchases]))<0.5
 ,[Customer Age])
 ,[Customer Age])

share|improve this answer

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Hubert. Unfortunately your script does not seem to work, returning a null value both in a table and in a KPI chart. It seems correct to me, but doesn't return the expected value.
  
  – FVerg
  Mar 29 at 15:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi @FVerg I put my qvf file with example on my onedrive 1drv.ms/u/s!Am4ZTQ8qcLwRs35Ms4wM8cEnIBxP
  
  – Hubert Dudek
  Mar 30 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ciao Hubert. Your qvf file works like a charm. If I try to replicate your formula on the dataset I showed in the first post, I don't get the same result. Putting the RangeSums in a table (With CustomerAge and # Purchases) it returns me a 0 for every row. I think that's the problem, but I don't get why it happens. Do you know why?
  
  – FVerg
  Apr 1 at 6:18
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please send screenshot of data model viewer so maybe I will figure out something.
  
  – Hubert Dudek
  Apr 1 at 7:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Done. If I use Sum([# Purchases]) in the RangeSums it seems to be working, but I still don't get why.
  
  – FVerg
  Apr 1 at 9:04
  

  
  
  
  

 | 
show 1 more comment

If you want to calculate median in such a scenario you need to weighted median and basically check which dimension value is in the middle:

Aggr(
 If(
 (Rangesum(
 Above([# Purchases],0,RowNo())
 )
 /Sum(TOTAL [# Purchases]))>=0.5 
 and 
 (Rangesum(
 Above([# Purchases],1,RowNo()-1))
 /Sum(TOTAL [# Purchases]))<0.5
 ,[Customer Age])
 ,[Customer Age])

share|improve this answer

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

Aggr(
 If(
 (Rangesum(
 Above([# Purchases],0,RowNo())
 )
 /Sum(TOTAL [# Purchases]))>=0.5 
 and 
 (Rangesum(
 Above([# Purchases],1,RowNo()-1))
 /Sum(TOTAL [# Purchases]))<0.5
 ,[Customer Age])
 ,[Customer Age])

share|improve this answer

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges

answered Mar 29 at 12:29

Hubert DudekHubert Dudek

75633 silver badges1313 bronze badges