If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I need your help on one of our scenario.
I have to combine two dimension into one in x axis.
Year and New Year.
If the user selects ID =1, then the output should be
How to achieve this scenario. Please help me on this.
I have attached the sample app. Please have a look.
@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;
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 ];
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.
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.
See attached QVF
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 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))
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.