Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
spikenaylor1
New Contributor III

Expression Issue used for CPV type chart

Any help for the below issue would be greatly appreciated

I am charting strength per batch

each Batch could have been made a with different strains. We have 4 categories of strain but could have 2 strains in one category

my chart shows strength for each batch sorted by autonumber(Strain)

I am wanting to show historical mean, SD, UCL and LCL Values on the chart. 

I have a separate imported table with the historical data, Year, Category, Mean, SD values

I can manage to display the Mean and SD etc for the Categogories.

This is my Expression to display the Mean Historical Value

however, we may need to display different Mean,SD for a any new strains coming in until we can incorporate the data into the correct Category.

I can achieve this with one new strain, but not with 2 new strains. (Example, just loading Strain 1 or Strain 2 gives the correct Mean displayed on the chart, loading both together causes the chart to ignore both sets of data for Strain 1 and Strain 2)

 

=
IF(BatchStrain <> CPVBatchStrain,

			If(Category = 'TypeA',
			Only({<CPVCategory={'TypeA'}>}StrengthMean)
			,
			If(Category = 'TypeB',
			only({<CPVCategory={'TypeB'}>}StrengthMean)
			,
			If(Category = 'TypeC',
			only({<CPVCategory={'TypeC'}>}StrengthMean)
			,
			only({<CPVCategory={'TypeD'}>}StrengthMean)
			)
			)
			)
,
only({<CPVBatchStrain=P(BatchStrain)>}StrengthMean)

)

 

 

field starting with CPV are from the loaded table for Historical CPV Data

example below

YearCPVCategoryCPVBatchStrainMeanSD
2019TypeA-8.120.14
2019TypeB-8.160.15
2019TypeC-8.450.3
2019TypeD-8.410.31
2019-Strain17.50.1
2019-Strain28.050.07