Load only one 'year field' for different territorial levels in QlikSense

[davide_ing]

Hi everyone,

I am wondering if there is a better solution for this problem: I have different data tables referring to different territorial levels (states, regions, provinces, cities, etc.).

Every level has year-related data:

• cities have number of inhabitants in 2022, 2021, etc.
• provinces have number of deaths in 2019, 2018, etc.
• etc.

In my model I loaded a 'YEAR' field for every level (STATE_YEAR, REGION_YEAR, etc.), but this force me to do multiple selection if I want to see all the data of a specific year.

Is there a better way to do that?

Thank in advance

[davide_ing]

Hi everyone,

I am wondering if there is a better solution for this problem: I have different data tables referring to different territorial levels (states, regions, provinces, cities, etc.).

Every level has year-related data:

• cities have number of inhabitants in 2022, 2021, etc.
• provinces have number of deaths in 2019, 2018, etc.
• etc.

In my model I loaded a 'YEAR' field for every level (STATE_YEAR, REGION_YEAR, etc.), but this force me to do multiple selection if I want to see all the data of a specific year.

Is there a better way to do that?

Thank in advance

[ogster1974]

In the simpliest way

concatenate your separate facts into one fact table  set a FACT_TYPE for each data set i.e  'number of inhabitants' as FACT_TYPE and 'number of deaths' as FACT_TYPE. Then alias your STATE_YEAR as Year and REGION_YEAR as Year.

Then you'll have one field to filter on.

[davide_ing]

But at the same time I would like to mantain the hierarchical relationship between STATE, REGION, etc. (i.e. regionA belongs to state1, regionB belongs to state2), so the the region table is linked to state table, province table is linked to region table, etc.

[ogster1974]

Include the geographical fields in your fact table and fill in the blanks based on your hierarchy using mapping tables.  For the measures that have no state add the label 'No State' so you can filter on it.

[davide_ing]

With 'include the geographical fields in the fact table' do you mean something like that?

STATE	REGION	FACT_TYPE	YEAR	VALUE
STATE1	REGION A	INDEX1	2019	100
STATE1	REGION A	INDEX1	2020	200
STATE1	REGION A	INDEX1	…	300
STATE1	REGION B	INDEX1	2019	50
STATE1	REGION B	INDEX1	2020	60
STATE1	REGION B	INDEX1	…	70

I haven't understood the other points; could you show me an example?