Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I am new to QlikSense and have this task that I'm wondering how should I do step by step. I have to build a KPI table and the output should look like this:
Deliverables/ Months | Jan | Feb | March |
KPI 1 | |||
KPI 2 | |||
KPI 3 |
And to calculate those metrics, I need different fields from different sources. For example, KPI 1 needs field A, field B from table 1. KPI 2 needs field C, field D from table 2 and so on. And the tables are not necessarily relating to others.
I'm wondering which can be the most optimized way to proceed in this situation . Should I load all the fields needed from all the tables, and then calculate KPI(s) under different measures or any other solutions?
Please advise.
Thank you a lot.
Best regards,
Nat
You need to know how to model your data for this. Search for Star Schema. Example of code:
Table1:
Load * INLINE [field A, field B, Date_table1, Key_table1
80, 100, 01-01-2022, 1
90,100,01-02-2022, 2
95,100,01-03-2022, 3
];
Table2:
LOAD * INLINE [field C, field D, Date_table2, Key_table2
50,80, 01-01-2022, 1
30,80, 01-02-2022, 2
60,120, 01-03-2022, 3
]
;
Fact_Dim:
LOAD * INLINE [FactID, FactName
1, KPI 1
2, KPI 2
];
Facts:
LOAD * INLINE [FactID];
Tmp_KPI1:
NoConcatenate
LOAD '1' as FactID
, Key_table1 as Key_table1
, Date_table1 as Date
, "field A"/"field B" as Value
Resident Table1
;
Concatenate (Facts)
LOAD *
Resident Tmp_KPI1
;
drop table Tmp_KPI1
;
Tmp_KPI2:
NoConcatenate
LOAD '2' as FactID
, Key_table2 as Key_table2
, Date_table2 as Date
, "field C"/"field D" as Value
Resident Table2
;
Concatenate (Facts)
LOAD *
Resident Tmp_KPI2
;
drop table Tmp_KPI2
;
And then you can use the tip given by @vinieme12 :
Just use a Pivot Table with dimension Month added to columns and drag each of your KPIs to Rows
You need to know how to model your data for this. Search for Star Schema. Example of code:
Table1:
Load * INLINE [field A, field B, Date_table1, Key_table1
80, 100, 01-01-2022, 1
90,100,01-02-2022, 2
95,100,01-03-2022, 3
];
Table2:
LOAD * INLINE [field C, field D, Date_table2, Key_table2
50,80, 01-01-2022, 1
30,80, 01-02-2022, 2
60,120, 01-03-2022, 3
]
;
Fact_Dim:
LOAD * INLINE [FactID, FactName
1, KPI 1
2, KPI 2
];
Facts:
LOAD * INLINE [FactID];
Tmp_KPI1:
NoConcatenate
LOAD '1' as FactID
, Key_table1 as Key_table1
, Date_table1 as Date
, "field A"/"field B" as Value
Resident Table1
;
Concatenate (Facts)
LOAD *
Resident Tmp_KPI1
;
drop table Tmp_KPI1
;
Tmp_KPI2:
NoConcatenate
LOAD '2' as FactID
, Key_table2 as Key_table2
, Date_table2 as Date
, "field C"/"field D" as Value
Resident Table2
;
Concatenate (Facts)
LOAD *
Resident Tmp_KPI2
;
drop table Tmp_KPI2
;
And then you can use the tip given by @vinieme12 :