Qlik Community

Ask a Question

App Development

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
andysaile
Partner
Partner

Pivot table with dimensions from two different fact contexts

I have a concatenated fact table with two fact contexts and corresponding dimensions:

     

FactNameCargoIDCargoGroupPathNameDateIDDamageTypeName
Transport1PackageA-->B20180101
Transport1PackageB-->C20180201
Transport1PackageC-->D20180301
Transport2EnvelopeA-->B20180201
Transport2EnvelopeB-->D20180301
Damage1PackageA-->B20180101Chipped
Damage1PackageA-->B20180101Scratched
Damage2EnvelopeA-->B20180101Crushed

I calculate the measure Count({<FactName = {'Transport'}, DamageTypeName =>} distinct CargoID) in a pivot table with dimensions DamageTypeName and CargoGroup. The result looks as follows:

Edit: Problem here is the fact context "Transport" does not have dimension values for DamageTypeName. Nevertheless I want calculate the total value for number of transported cargo to show for each DamageTypeName.

What I want it to look like is:

Attached is an example, which I used to test different behaviours.

Does anyone have an idea?

Kind regards,
Andy

3 Replies
petter
MVP
MVP

I believe this might be what you are looking for then:

2018-04-18 17_12_39-Qlik Sense Desktop.png

You don't need the dimension CargoGroup in the Pivot Table.

Define three measures:

Expression: Count(TOTAL {<CargoGroup={*}>} distinct CargoID)         

Label: Gesamtwerte

Expression: Count({<FactName={'Damage'},CargoGroup={'Package'}>} DISTINCT CargoID)

Label: Package

Expression: Count({<FactName = {'Damage'},CargoGroup={'Envelope'}>} DISTINCT CargoID)

Label: Envelope

petter
MVP
MVP

or with a single measure and keeping the CargoGroup dimension:

Expression:

If( ColumnNo()=0,   // column 0 is the Totals column

  Count(TOTAL {<FactName = {'Damage'}>} DISTINCT CargoID)

,Count({<FactName = {'Damage'}>} DISTINCT CargoID)

)


2018-04-18 17_30_18-Qlik Sense Desktop.png

andysaile
Partner
Partner
Author

Hello Petter,

thanks for your response. But I actually need the total measure from FactName = {'Transport'} to show for the dimension DamageTypeName. Problem here is, that the dimension has only values for fact context "Damage".

I edited the original post to clarify this.