Solved: Load Inline Table Formula Help - Qlik Community

pgalvezt2021

Hello,

I have an inline table with the following data: Product Code, Period, and Total.

TempData:
LOAD*
INLINE [
Cod_Product, Period, Total
A, 202004, 675
A, 202007, 646
A, 202104, 668
A, 202404, 570
B, 202108, 901
B, 202008, 955
B, 202504, 597
B, 202203, 682
C, 202511, 135
C, 202311, 551
C, 202510, 292
C, 202001, 867
D, 202502, 912
D, 202202, 126
D, 201805, 681
D, 202507, 406
];

I would like to know if you can help me find the formula that calculates the maximum periods per product, regardless of which period I select in the filter. For example:

Example if I choose 202502
202502 D
202311 C
202203 B
202104 A

Example if I choose 202203
202202 D
202001 C
202203 B
202104 A

Example if I choose 202008
201805 D
202001 C
202008 B
202007 A

Example with no period selected

202507 D
202511 C
202504 B
202404 A

And when this is done, I want the total sum of those maximum periods to appear.

I attach an Excel file for better understanding.

Thank you!!!!

howdash

Video

Solution is below and the video demoes the solution and how it works.

Solution

As for the solution, try this 👇

Script

// load data and create a field to map Totals to Product and Period
TempData:
LOAD *,
	AutoNumber(Cod_Product & '|' & Period)	as key
INLINE [
Cod_Product, Period, Total
A, 202004, 675
A, 202007, 646
A, 202104, 668
A, 202404, 570
B, 202108, 901
B, 202008, 955
B, 202504, 597
B, 202203, 682
C, 202511, 135
C, 202311, 551
C, 202510, 292
C, 202001, 867
D, 202502, 912
D, 202202, 126
D, 201805, 681
D, 202507, 406
];


mapProductPeriodToTotal:
Mapping
Load key,
	Total
Resident TempData
;

// loop trough each period to find max Period per product
For Each vPeriod in FieldValueList('Period')

	maxValues_temp:
    Load '$(vPeriod)'								as Period,
    	Cod_Product									as MaxCod_Product,
    	Max(Period)									as MaxPeriod
    Resident TempData
       Where Period <= $(vPeriod)
    Group By Cod_Product
    ;

Next vPeriod


// map Totals to product and Max Period within a period
maxValues:
Load Period,
	MaxCod_Product,
	MaxPeriod,
    ApplyMap(
    	'mapProductPeriodToTotal',
        AutoNumber(MaxCod_Product & '|' & MaxPeriod),
        Null()
        )											as MaxPeriodTotal
Resident maxValues_temp
Order By Period,
	MaxCod_Product
;

Drop Table maxValues_temp;

This will create a table that will have max period and corresponding total for each period and product.

Frontend

On the frontend, add a Filter Pane with Period as the dimension. Then add a table with MaxCod_Product, MaxPeriod, and MaxPeriodTotal dimensions.

When user will select a period, the table will update to show max Period and corresponding Total for selected period.

Limitation

The only thing that this solution won't do is show the max Period and Total per product when no Period is selected. A way to work around that is to enable Always One Value Selected setting for Period field and select a max period. Or have a bookmark that will trigger when an app is opened and automatically select the max available Period for user.

View solution in original post

Amit_Prajapati

Hi Just try with below one

TempData:
LOAD * INLINE [
Cod_Product, Period, Total
A, 202004, 675
A, 202007, 646
A, 202104, 668
A, 202404, 570
B, 202108, 901
B, 202008, 955
B, 202504, 597
B, 202203, 682
C, 202511, 135
C, 202311, 551
C, 202510, 292
C, 202001, 867
D, 202502, 912
D, 202202, 126
D, 201805, 681
D, 202507, 406
];

MaxPeriodPerProduct:
LOAD
Cod_Product,
Max(Period) as Max_Period
RESIDENT TempData
GROUP BY Cod_Product;

JOIN (MaxPeriodPerProduct)
LOAD
Cod_Product,
Period as Max_Period,
Total as Max_Total
RESIDENT TempData;

MaxPeriodTotalSummary:
LOAD
Sum(Max_Total) as Total_Max_Periods
RESIDENT MaxPeriodPerProduct;

I'm getting below output for the period 202502

Kaushik2020

Hi @Amit_Prajapati we should see something like below.

202502	D	912
202311	C	551
202203	B	682
202104	A	668

we need to tell chatGpt for a clear answer 🙂

Kaushik2020

if you are using a table then use

dimension as cod_Product || Measure Period : =Max(TOTAL <Cod_Product>{<Period = {"<=$(=Alt(Only(Period), Max(TOTAL Period)))"}>} Period)

pgalvezt2021

Hello, thanks for the response. I think there might be a mistake, could it be? Look: if I don’t filter anything, it should show for 202507 and code D the number 406. D, 202507, 406 for C = 135, etc.
Now, if I filter July 2025, it should show July 2025 for D = 406, for C = 551, for B = 597, and for A = 570.
This is because, according to what I’m filtering, it should return the maximum periods for each code. Since I filtered July 2025, October and November 2025 and the codes associated with those periods should no longer be considered.

howdash

Your question and examples seem to contradict each other.

You asked how you can calculate "maximum periods per product, regardless of which period I select". In other words, no matter what period you select, the maximum period per product should always be shown.

The maximum period for product A, for example, is 202404. So no matter what period you select product A should always show 202404.

However, in the examples you select periods and different period (not maximum) is shows for each product. The examples then suggest that period selections are considered.

Which one are you looking for? For selections to be considered or not considered?

hanna_choi

Hello @pgalvezt2021

How about this ways?

when you are selected one values from filter panel, the table values is changed.

I am using the max (), getfieldselections() functions and vPeriod variable.

vPeriod : GetFieldSelections(Period)

pgalvezt2021

Hello,

The maximum period for product A, for example, is 202404. So, regardless of which period you select, product A should always display 202404.
R: Everything is correct as you mentioned. However, for example, if I select 202401, then 202404 would no longer be the maximum period, and therefore A should return the maximum period corresponding to 668, but the formula gives 675 instead. In the Excel file, I show you the example.

pgalvezt2021

Hi, thanks for your response!!! Let me explain: For the filter you applied, Qlik should show the following:

Filter: 202502

For code A, it should display 570, as it is the maximum period for A according to the current selection, which is 202502.
For code B, it should display 682, corresponding to period 202203.
For code C, it should display 551, corresponding to the maximum period according to your filter, which would be 202311.
For code D, it should display 912, as it corresponds to the maximum period based on your selection.

pgalvezt2021

Hi, I forgot to mention that the periods shown by the formula are correct according to the filter I apply; the issue is that it does not display the totals associated with those maximum periods.

Load Inline Table Formula Help

Visualization