Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Aggregating by condition

Two weeks ago I create a discussion about a aggregation for a dimension not included in fact table.

Today, I am searching a similar solution for a dataset with more complexity.

I have a Fact Table that concatenating three different data sources.

Using the same example from my previous post:

Fact Table:

   

FACT TABLE
Id_PersonSpecialtyAgeGenderCostAffected
1Pediatrics3Male10001
2 1Female 1
3Emergencies14Male20001
4 33Male 1
5Gynecology20Female30001
6 45Male 1
7Oncology67Male60001
8Geriatrics74Female5001

 

Fact table contain a concatenation of 3 differents data sources. Only one of them has Specialty (and Cost) information.

Dimension:

   

DIMENSION TABLE Specialty
DimensionCondition
Pediatrics<15
Gynecology>15 & Female
Geriatrics>65
OncologyALL
EmergenciesALL

I need a table with two expression that calculates Cost by Specialty and Affected People by Specialty (It is not a count of fact table, it is a record counter filtered by condition). So:   

   

Expected
SpecialtySum(Cost)Sum(Affected)
Pediatrics10003
Emergencies20008
Gynecology30002
Oncology60008
Geriatrics5002
Pediatrics30008
Emergencies
Pediatrics40005 (3+2)
Gynecology
Oncology65008
Geriatrics
Pediatrics45006 (3+2+2-1, this one is for a woman in Gyn & Ger)
Gynecology
Geriatrics

I try to get this result with a Sum(Cost) and pick(match(Affected)) but pick & match doesn't work fine in a table with Specialty.

How can I get these two expression correctly?

Thank you in advance.


Tags (1)
Community Browser