Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
germanboxers
Contributor III
Contributor III

*Help* Scaling a normal distribution plot to the bar chart maximum

I am trying to scale a normal distribution plot to the maximum value of the bar chart.  I have charts like these for multiple manufacturing units and have used variable to allow the user to filter what to exclude (e.g. +/-10 % error, +/- 5% error, etc) and for how many months to review. 

The below image is what I have so far.  It is achieved by using the NormDist function and multiplying it by a constant and the total count of coils.  I would like to scale it so the height of the Bell curve is exactly equal to the maximum bar.  
 germanboxers_0-1701210218899.png

In excel I would just multiply it by the ratio of the highest bar divided by the largest value of the bell curve.  I'm not quite sure how to do that in Qlik.   This is what I'm currently using to get the Bell curve above

=Only(NormDist(Round(WgtDiffPct,.001)

, $(vCPLMeanPct)
, $(vCPLSigmaPct)
,0)

*.0012*(Count(total{$<facility_cd={'P'},report_production_date={">=$(=MonthStart(Max(Today()),$(=(v_Months_To_Use))))<=$(=MonthEnd(Max(Today())))"},
WgtDiffPct={">=-$(=(v_FilterPct))<=$(=(v_FilterPct))"}>} coil_no)))

 

I thought about using something like Max(Aggr(Count{...},Round(WgtDiffPct,.001))), but Qlik didn't like that.  I'm guessing that is because it sees Round(WgtDiffPct,.001) as a calculated dimension.

Any suggestions on how to do this?

Thanks a bunch!

 

Labels (1)
1 Solution

Accepted Solutions
germanboxers
Contributor III
Contributor III
Author

I took another stab at this a few moments ago and got it to work.  This was the solution to scale the bell curve to the highest individual bar on the raw data:

 
=Only(NormDist(Round(WgtDiffPct,.001) 
      , $(vCPLMeanPct) 
      , $(vCPLSigmaPct)
      ,0))
      
      *Max(Total aggr(Count({$<facility_cd={'P'},report_production_date={">=$(=MonthStart(Max(Today()),$(=(v_Months_To_Use))))<=$(=MonthEnd(Max(Today())))"},
WgtDiffPct={">=-$(=(v_FilterPct))<=$(=(v_FilterPct))"}>} coil_no),WgtDiffPctRnd))
        
      /Max(Total aggr(Only(NormDist(Round(WgtDiffPct,.001) 
      , $(vCPLMeanPct) 
      , $(vCPLSigmaPct)
      ,0)),WgtDiffPctRnd))
 
The Total qualifier is needed to get one record (just the max) across all dimension values.  Works like a charm.
germanboxers_0-1701442045075.png

 

View solution in original post

4 Replies
germanboxers
Contributor III
Contributor III
Author

Anyone have any suggestions??


Thanks.

marcus_sommer

You may try as Bell-Expression a synchronizing-approach with something like:

rangemin(Bell-Expression, CountCoils-Expression)

germanboxers
Contributor III
Contributor III
Author

Thanks Marcus.  I actually figured out how to do it this morning after I bumped the thread.  I'll post the solution in a moment.

germanboxers
Contributor III
Contributor III
Author

I took another stab at this a few moments ago and got it to work.  This was the solution to scale the bell curve to the highest individual bar on the raw data:

 
=Only(NormDist(Round(WgtDiffPct,.001) 
      , $(vCPLMeanPct) 
      , $(vCPLSigmaPct)
      ,0))
      
      *Max(Total aggr(Count({$<facility_cd={'P'},report_production_date={">=$(=MonthStart(Max(Today()),$(=(v_Months_To_Use))))<=$(=MonthEnd(Max(Today())))"},
WgtDiffPct={">=-$(=(v_FilterPct))<=$(=(v_FilterPct))"}>} coil_no),WgtDiffPctRnd))
        
      /Max(Total aggr(Only(NormDist(Round(WgtDiffPct,.001) 
      , $(vCPLMeanPct) 
      , $(vCPLSigmaPct)
      ,0)),WgtDiffPctRnd))
 
The Total qualifier is needed to get one record (just the max) across all dimension values.  Works like a charm.
germanboxers_0-1701442045075.png