Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashley33
Contributor II
Contributor II

To Display common records In a third table C using intersection of row results already filtered in two other displayed tables A and B

Hello Dears,

Please note that i have loaded the attached excel data StockData.xlsx in load script:

TableStock:
LOAD
Country,
"Year",
"Month",
"Item Name",
IC,
"Stock Qty",
DataType
FROM [lib://DATA/bkup/StockData.xlsx]
(ooxml, embedded labels, table is TableStock);

TableStockComparison:
LOAD
CountryComparison,
YearComparison,
MonthComparison,
"Item Name Comparison",
"IC Comparison",
"Stock Qty Comparison",
"DataTypeComparison"
FROM [lib://DATA/bkup/StockData.xlsx]
(ooxml, embedded labels, table is TableStockComparison);

 

Then i created below qliksense page:

Ashley33_0-1623913018066.png

Can you please help me solve the following 3 user requirements:

 

1. TableA user requirements:

Using TableStock column filters on the left pane (Country, Year, Month),  how to Write Expression to display item names having avg(IC) <= 100 from script loaded TableStock ?

Example: Using TableStock filters on left pane in screenshot ,  for Australia only the two records with avg(IC) <= 100 is displayed for the two items named: Apple and Orange

Note: The records having avg(IC) = null should be excluded from displayed Table A

CountryYearMonthItem Name=avg(IC)sum([Stock Qty])
Australia20203Apple75.0016719
Australia20203Orange90.0014118

 

2. TableB user requirements:

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison), how to Write Expression to display item names comparisons having avg(IC Comparison) > 300 from script loaded TableStockComparison

Example: Using TableStockComparison filters on right pane in screenshot ,  for England only the one record with avg(IC Comparison) > 300 is displayed for the one item named: Orange

Note: The records having avg(IC Comparison) = null should be excluded from displayed Table B

CountryComparisonYearComparisonMonthComparisonItem Name Comparison=avg([IC Comparison])sum([Stock Qty Comparison])
England20203Orange400.0088790

 

3. TableC user requirements:

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison)

And

Using TableStockComparison column filters on the right pane (CountryComparison, YearComparison, MonthComparison),

how to Write an Expression to display COMMON item names (that is the intersection of common item names) from the results of the above displayed Table A and Table B

Example: Using the example filter selections from above, the below  common item Orange should be displayed in the 3rd table C as it exists and is common in both tables A for Australia data and Table B for England data

Note: The records having avg(IC) = null and avg(IC Comparison) = null should be excluded from displayed Table C

CountryYearMonthItem Nameavg(IC) <= 100sum([Stock Qty])CountryComparisonYearComparisonMonthComparisonItem Name Comparisonavg([IC Comparison]) > 300sum([Stock Qty Comparison])
Australia20203Orange90.0014118England20203Orange400.0088790

 

0 Replies