Qlik Community

Qlik Sense App Development

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

andysaile
New Contributor

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

Tags (2)
3 Replies
MVP
MVP

Re: Pivot table with dimensions from two different fact contexts

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

MVP
MVP

Re: Pivot table with dimensions from two different fact contexts

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
New Contributor

Re: Pivot table with dimensions from two different fact contexts

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.

Community Browser