Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharathkamath28
Contributor III
Contributor III

Combining Two Dimension into One in Combo Chart Qliksense

Hi All/@tresesco  @sunny_talwar 

I need your help on one of our scenario.

I have to combine two dimension into one in x axis.

Year and New Year.

bharathkamath28_0-1601446887899.png

If the user selects ID =1, then the output should be 

bharathkamath28_1-1601448155537.png

 

How to achieve this scenario. Please help me on this.

I have attached the sample app. Please have a look.

 

Thanks

 

 

Labels (1)
2 Solutions

Accepted Solutions
sunny_talwar

@bharathkamath28 Have you tried selecting ID2? Did it not work when you selected ID 2?

When ID = 2

image.png

When ID = 1

image.png

View solution in original post

sunny_talwar

@bharathkamath28 There might be a better LinkID you can create, but based on the data provided... try this

Base1:
LOAD RowNo() as LinkID,
	 *;
load * inline [
ID,Value,Year
1,10,2020
2,15,2021];

B:
LOAD RowNo() as LinkID,
	 *;
Load * inline [
ID,ID2,Bal,NewYear
1,1.1,25,2020
1,1.2,30,2021
1,1.3,40,2022
2,2.1,50,2020
2,2.2,60,2024
2,2.3,70,2025];

LinkTable:
LOAD DISTINCT Year as LinkYear,
	 LinkID,
     ID,
     'Base1' as Table
Resident Base1;

Concatenate (LinkTable)
LOAD DISTINCT NewYear as LinkYear,
	 LinkID,
     ID,
     'B' as Table
Resident B;

DROP Field ID from Base1;
DROP Field ID from B;

View solution in original post

15 Replies
tresesco
MVP
MVP

May be like:

A:
load * inline [
ID,Value,Year
1,10,2020
2,15,2021
];
B:
Concatenate
Load *,
    NewYear as Year,
    Bal as Value
    inline [
ID,ID2,Bal,NewYear
1,1.1,25,2020
1,1.1,30,2021
1,1.3,40,2022
2,2.1,50,2020
2,2,2,60,2024
2,2.3,70,2025
];
bharathkamath28
Contributor III
Contributor III
Author

I don't want to change the data model because it messes up all my other logic and charts.

I have more than 15 tables in my data model.

So, without modifying the data model can we use expressions to achieve this scenario.

Please help.

Vegar
MVP
MVP

You need to create a common dimension somewhat. You can achieve this by either concatinating the two transaction tables into a single transaction table or  try to create a commoo dimension table that links the A and B to a common dimension. 

Below an example on how you can do the concatination.

Vegar_0-1601452583813.pngVegar_1-1601452702315.png

 

See attached QVF

tresesco
MVP
MVP

You could probably achieve something close to that in the UI, however, I would strongly discourage that. This should be dealt in the script. Otherwise, this is going to be a bad data model and as a result there would be many things in the UI that you would find difficulty with.

bharathkamath28
Contributor III
Contributor III
Author

@tresesco  @Vegar 

Below is my 4 Tables in my Data model.

bharathkamath28_0-1601455287364.pngbharathkamath28_1-1601455321426.png

when the user logins always the default Name= A will be selected.

And User might select ID2 to analyse the data

Then how should be the data model to achieve this scenario.

bharathkamath28
Contributor III
Contributor III
Author

Please find the attached sample data

sunny_talwar

@bharathkamath28 May be create an island table which doesn't hamper your data model

IslandYear:
LOAD DISTINCT Year as IslandYear
Resident Base1;

Concatenate (IslandYear)
LOAD DISTINCT NewYear as IslandYear
Resident B;

and then use IslandYear as dimension with two measures

Sum(If(IslandYear = Year, Value))
Sum(If(IslandYear = NewYear, Bal))

image.png

bharathkamath28
Contributor III
Contributor III
Author

@sunny_talwar 

This will work fine but the user might click on other dimensions like ID2.

As this is the island table and not associated with our main data model.

I need this chart to be associated with our main data model.

sunny_talwar

@bharathkamath28 Have you tried selecting ID2? Did it not work when you selected ID 2?

When ID = 2

image.png

When ID = 1

image.png