Hello community,
I'm currently developing a P&L dashboard on Qlik Sense and I have the question that follows:
Lets suppose i have 4 lines in my P&L - Groos Sales, Variable Costs, Fixed Costs and Operating Profit. So my fact table is something like this:
Fact_PeL:
KPI Name Client KPI Value KPI Type
GS X 10 Actual
VC Y 8 Profit
VC Z 5 Actual
...
The problem is that the user requested to the P&L be somehow dynamic, so i have to add two filters so values uploaded from a flat file can be inserted in some accounts, basicly chaging the standard value.
By default the effects are already within the fact table, so my first step is to set the filters do included. Lets suppose the two filters are called FX Effect and Import Effect
One way to set the default filters is to just add the fields to the fact table with de 'Included' value
Fact_Pel2:
LOAD
*,
'Included' as [FX Effect],
'Included' as [Import Effect]
RESIDENT Fact_PeL;
DROP TABLE PeL;
Now I want to include the exclude effect, the values for excluded for examples comes from flat files, so i just concatenate then to the fact table.
Concatenate(Fact_PeL2)
LOAD
[Kpi Name],
[KPI Value],
[KPI Type],
'Excluded' as [FX Effect]
FROM [FLAT EXCLUDED FX]
The same process is used to Imports effects.
Now I can compose expressions that works as intended, but my problem is that the Flat Files does not cover all filter combination possibities and i have a table with coloring conditions that doesn't work when i select a filter combination that doesnt exist.
To fix it I built two inline tables
D_effect:
LOAD * INLINE[
FX Effect
Included
Excluded
];
D_import:
LOAD * INLINE[
Import Effect
Included
Excluded
];
after creating them i built a fact table just with the dimensions values and left joined them, the problem is that now my fact that had something like 1M rows goes to more than 8M rows.
Fact_temp:
LOAD
KPI Name,
Kpi Type,
Client,
Resident Fact_PeL;
left join(Fact_temp)
LOAD * RESIDENT D_effect;
left join(Fact_temp)
LOAD * RESIDENT Fact_import;
Is the other solution to compose the filter combination w/o increasing my fact this much ?
Thanks in advance n sorry for my bad english !
