Using Multiple Excel Sheets with Different Granularity in Qlik Sense
Can Qlik Sense use multiple sheets from the same Excel file as data sources for different visualizations (for example, a chart from one sheet and a table from another) within the same application?
What is the possible way if we have data with different granularities:
Monthly detail data in one sheet Aggregated Period data (H1, H2, Annual, Recent3) in another sheet
Any recommendations or examples would be appreciated.
As per the shared logic and requirements, I created the fact table accordingly. However, the table and chart are based on different source data and calculation logic.
The H1 chart is created from the Period/Snapshot sheet. The H1 judgment is calculated using only the September snapshot data (9th month). Therefore, H1 contains only the judgments that exist in the September snapshot, such as 〇 (Maru) and △ (Triangle).
The detail table is created from the Monthly sheet. For H1, the table displays records from April to September based on the H1 flag. As a result, the table can contain judgments such as 〇 (Maru), △ (Triangle), and ✕ (Cross) that occurred in any month within the H1 period.
When a user selects ✕ (Cross) in the table, Qlik Sense applies that selection across all visualizations. However, the H1 chart is based only on the September snapshot data, where no ✕ (Cross) judgment exists. Therefore, no matching records remain for the H1 chart, causing the H1 bar to disappear.
By adding an appropriate source-information within each of the loads, like:
'detail' as Source
as well as:
'aggregated' as Source
the parts could be differentiated in dimensions, selections and conditions and/or states.
For the majority of views it would be sufficient to select the wanted Source part or adding it within a set analysis, like: sum({< Source = {'detail'}>} Value).
